//scan location cut
public Tuple <int, String, double, double, DateTime> ScanLocationsCut(int id, DateTime time)
{
int scanLocID = id;
String jointName1 = "default";
double distJoint1 = 0;
double distJoint2 = 0;
DateTime timestamp = new DateTime();
this.con = new SqlCeConnection();
this.con.ConnectionString = "Data Source=|DataDirectory|\\Patients.sdf";
this.con.Open();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "SELECT scanLocID, jointName1, distJoint1, distJoint2, timestamp FROM ScanLocations WHERE scanLocID LIKE @ID AND timestamp LIKE @Time";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@ID", id);
selectQuery.Parameters.Add("@Time", time.Date.ToString("yyyy-MM-dd HH:mm:ss"));
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
scanLocID = reader.GetInt32(0);
jointName1 = reader.GetString(1);
distJoint1 = reader.GetDouble(2);
distJoint2 = reader.GetDouble(3);
timestamp = Convert.ToDateTime(reader.GetDateTime(4).ToString());
}
reader.Close();
return(Tuple.Create(scanLocID, jointName1, distJoint1, distJoint2, timestamp));
}
public List <DailyUsageBO> AllByMonth(string username)
{
List <DailyUsageBO> result = new List <DailyUsageBO>();
DailyUsageBO record;
CultureInfo provider = CultureInfo.InvariantCulture;
try
{
using (SqlCeCommand cmd = new SqlCeCommand(String.Format("SELECT month, SUM(upload), SUM(download), SUM(total) FROM {0} GROUP BY month ORDER BY month", username),
DataBaseFactory.Instance.GetConnection()))
{
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
record = new DailyUsageBO();
record.Month = dr.GetString(0);
record.Upload = dr.GetDouble(1);
record.Download = dr.GetDouble(2);
record.Total = dr.GetDouble(3);
record.Day = DateTime.ParseExact(record.Month, "yyyyMM", provider);
result.Add(record);
}
}
}
}
catch
{
CreateTable(username);
}
return(result);
}
//scan location
public Tuple <LinkedList <String>, LinkedList <String>, LinkedList <String>, LinkedList <double>, LinkedList <double>, LinkedList <double>, LinkedList <DateTime> > ScanLocations(String colName, String criterion)
{
LinkedList <int> scanLocID = new LinkedList <int>();
LinkedList <String> boneName = new LinkedList <String>();
LinkedList <String> jointName1 = new LinkedList <String>();
LinkedList <String> jointName2 = new LinkedList <String>();
LinkedList <double> distJoint1 = new LinkedList <double>();
LinkedList <double> distJoint2 = new LinkedList <double>();
LinkedList <double> jointsDist = new LinkedList <double>();
LinkedList <DateTime> timestamp = new LinkedList <DateTime>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "SELECT * FROM ScanLocations WHERE @ColName LIKE @Criterion";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@ColName", colName);
selectQuery.Parameters.Add("@Criterion", criterion);
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
scanLocID.AddLast(reader.GetInt32(0));
String b;
try
{
b = reader.GetString(1);
}
catch (Exception e)
{
b = "null";
}
boneName.AddLast(b);
jointName1.AddLast(reader.GetString(2));
jointName2.AddLast(reader.GetString(3));
distJoint1.AddLast(reader.GetDouble(4));
distJoint2.AddLast(reader.GetDouble(5));
double d;
try
{
d = reader.GetDouble(6);
}
catch (Exception e)
{
d = 0;
}
jointsDist.AddLast(d);
timestamp.AddLast(Convert.ToDateTime(reader.GetDateTime(7).ToString()));
}
reader.Close();
//scanLocID not returned (to keep it under 8) !!!
return(Tuple.Create(boneName, jointName1, jointName2, distJoint1, distJoint2, jointsDist, timestamp));
}
private void lstBoxUser_PreviewMouseDown(object sender, MouseButtonEventArgs e)
{
var item = ItemsControl.ContainerFromElement(this.lstBoxUser, e.OriginalSource as DependencyObject) as ListBoxItem;
if (item != null)
{
User selectedUser = (User)item.DataContext;
// Open the connection using the connection string.
using (SqlCeConnection newCon = new SqlCeConnection(conString))
{
newCon.Open();
// Read in all values in the table.
using (SqlCeCommand com = new SqlCeCommand("SELECT date,hid,min,uid,steps,distance,calories FROM History WHERE uid = @id", newCon))
{
com.Parameters.AddWithValue("@id", selectedUser.Id);
List <Session> sessions = new List <Session>();
Session curSession = null;
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
DateTime date = reader.GetDateTime(0);
int index = reader.GetInt32(1);
int min = reader.GetInt32(2);
int id = reader.GetInt32(3);
int count = reader.GetInt32(4);
double distance = reader.GetDouble(5);
double calorie = reader.GetDouble(6);
History record = new History(id, date, min, index, count, distance, calorie);
if (null == curSession)
{
curSession = new Session(record.ID, record.Date, record.Index);
}
else if (curSession.ID != record.ID ||
curSession.Index != record.Index ||
curSession.Date != record.Date)
{
sessions.Add(curSession);
curSession = new Session(record.ID, record.Date, record.Index);
}
curSession.addRecord(record);
}
sessions.Add(curSession);
this.lstBoxHistory.ItemsSource = sessions;
}
}
}
}
private DailyUsageBO ReadRecord(SqlCeDataReader reader)
{
DailyUsageBO result = new DailyUsageBO();
result.Day = reader.GetDateTime(0);
result.Month = reader.GetString(1);
result.Upload = reader.GetDouble(2);
result.Download = reader.GetDouble(3);
result.Total = reader.GetDouble(4);
if (!reader.IsDBNull(5))
{
result.Period = new Period(reader.GetString(5));
}
return(result);
}
private double getSellingPrice(string itemid)
{
double sprice = 0;
dboperation operation = new dboperation();
SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath);
string query = "SELECT selling_price FROM sub_catagory WHERE ItemId=@id";
SqlCeDataReader reader = null;
try
{
SqlCeCommand cmd = new SqlCeCommand(query, conn);
cmd.Parameters.AddWithValue("@id", itemid);
reader = cmd.ExecuteReader();
while (reader.Read())
{
sprice = reader.GetDouble(0);
}
}
catch (Exception ex)
{
MessageBox.Show("Exception Happend while getting the Selling Price " + ex);
}
return(sprice);
}
public Tuple <LinkedList <int>, LinkedList <String>, LinkedList <DateTime>, LinkedList <double> > scanValueForPatient(int ScanID)
{
LinkedList <int> scanID = new LinkedList <int>();
LinkedList <String> pointCloudFileReference = new LinkedList <String>();
LinkedList <DateTime> timestamp = new LinkedList <DateTime>();
LinkedList <double> value = new LinkedList <double>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "Select Scans.scanID, pointCloudFileReference, timestamp, Records.value from Scans join Records on Scans.scanID = Records.scanID where Scans.scanID = @ScanID;";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@ScanID", ScanID);
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
scanID.AddLast(reader.GetInt32(0));
pointCloudFileReference.AddLast(reader.GetString(1));
timestamp.AddLast(Convert.ToDateTime(reader.GetDateTime(2).ToString()));
value.AddLast(reader.GetDouble(3));
}
reader.Close();
return(Tuple.Create(scanID, pointCloudFileReference, timestamp, value));
}
///////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
private static double _GetProjectVersion(string sqlConnStr)
{
Debug.Assert(sqlConnStr != null);
using (SqlCeConnection conn = new SqlCeConnection(sqlConnStr))
{
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(
QUERY_PROJECT_VERSION, conn))
{
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
double version;
if (reader.Read())
{
version = reader.GetDouble(0);
}
else
{
throw new DataException(Properties.Messages.Error_GetProjectVersionFailed);
}
return(version);
}
}
}
}
public static void SetTDispositivo(TDispositivo dispositivo, SqlCeDataReader dr, SqlCeConnection conn)
{
dispositivo.DispositivoId = dr.GetInt32(0);
dispositivo.Nombre = dr.GetString(1);
dispositivo.Empresa = dr.GetString(2);
dispositivo.Instalacion = GetTInstalacion(dr.GetInt32(3), conn);
dispositivo.Tipo = GetTTipoDispositivo(dr.GetInt32(4), conn);
dispositivo.Funcion = dr.GetString(5);
dispositivo.Estado = dr.GetString(6);
dispositivo.CodBarras = dr.GetString(7);
if (dr[14] != DBNull.Value)
{
dispositivo.Posicion = dr.GetString(14);
}
if (dr[8] != DBNull.Value)
{
dispositivo.FechaCaducidad = dr.GetDateTime(8);
}
dispositivo.Caducado = dr.GetBoolean(9);
if (dr[12] != DBNull.Value)
{
dispositivo.Operativo = dr.GetBoolean(12);
}
//if (!CntSciTerminal.FechaNula(dispositivo.FechaBaja))
// dispositivo.FechaBaja = dr.GetDateTime(10);
if (dr[10] != DBNull.Value)
{
dispositivo.FechaBaja = dr.GetDateTime(10);
}
if (dr[11] != DBNull.Value)
{
dispositivo.Modelo = GetTModeloDispositivo(dr.GetInt32(11), conn);
}
dispositivo.Abm = dr.GetByte(15);
// nuevos campos vrs 2018.0.1.0
if (dr[16] != DBNull.Value)
{
dispositivo.NIndustria = dr.GetString(16);
}
if (dr[17] != DBNull.Value)
{
dispositivo.CargaKg = dr.GetDouble(17);
}
if (dr[18] != DBNull.Value)
{
dispositivo.Fabricante = GetTFabricante(dr.GetInt32(18), conn);
}
if (dr[19] != DBNull.Value)
{
dispositivo.FechaFabricacion = dr.GetDateTime(19);
}
if (dr[20] != DBNull.Value)
{
dispositivo.AgenteExtintor = GetTAgenteExtintor(dr.GetInt32(20), conn);
}
}
private void searchItemcb_TextChanged(object sender, EventArgs e)
{
if (searchItemcb.Text == "")
{
currentstocklbl.Text = "";
}
dboperation operation = new dboperation();
SqlCeConnection conn = null;
SqlCeDataReader reader = null;
conn = operation.dbConnection(Settings.Default.DatabasePath);
string query = "SELECT sub_catagory.ItemId, sub_catagory.ItemName, sub_catagory.stock_Quantity, sub_catagory.purchase_price, sub_catagory.selling_price, catagory.MeasuringUnit, catagory.Catagory, catagory.sgst, catagory.cgst FROM sub_catagory INNER JOIN catagory ON catagory.CatId = sub_catagory.undercatagory WHERE sub_catagory.ItemName = @itemname";
try {
SqlCeCommand cmd = new SqlCeCommand(query, conn);
char[] splitchar = { '_' };
string itemcat = searchItemcb.Text;
string[] temp = itemcat.Split(splitchar);
// Regex.Match(searchItemcb.Text, @"\b($ $)\b")
cmd.Parameters.AddWithValue("@itemname", temp[0]);
reader = cmd.ExecuteReader();
while (reader.Read())
{
itemid = reader.GetInt64(0).ToString();
stock_qty = double.Parse(reader.GetString(2));
currentstocklbl.Text = "Current Stock of the " + temp[0] + " is " + stock_qty.ToString() + " " + reader.GetString(5).ToString();
billcat.Text = reader.GetString(6);
spricetb.Text = reader.GetDouble(4).ToString();
mu.Text = reader.GetString(5).ToString();
sgsttb.Text = reader.GetDouble(7).ToString();
cgsttb.Text = reader.GetDouble(8).ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("" + ex);
}
}
public Model.Memory.Nature GetNatue()
{
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "SELECT TOP(1) * FROM [nature]";
SqlCeDataReader reader = command.ExecuteReader();
reader.Read();
return(new Model.Memory.Nature()
{
Intellect = reader.GetDouble(reader.GetOrdinal("intellect")),
Courage = reader.GetDouble(reader.GetOrdinal("courage")),
Godliness = reader.GetDouble(reader.GetOrdinal("godliness")),
Sociality = reader.GetDouble(reader.GetOrdinal("sociality")),
Peaceness = reader.GetDouble(reader.GetOrdinal("peaceness")),
Curiosity = reader.GetDouble(reader.GetOrdinal("curiosity")),
Hunting = reader.GetDouble(reader.GetOrdinal("hunting")),
Volatility = reader.GetDouble(reader.GetOrdinal("volatility")),
Forgetness = reader.GetDouble(reader.GetOrdinal("forgetness")),
});
}
// 填写cells的内容，具体为time时间、union工会下的经费信息，按groupBy分组
private void FillCells(Excel.Range cells, DateTime time, string union, string groupBy)
{
cells[1, 1] = "纳税人名称";
cells[1, 2] = "实缴金额";
string sql = @"SELECT c.CompanyName, f.Received FROM CompanyInfo c INNER JOIN Funds f
ON c.CompanyID = f.CompanyID WHERE f.Time = @time AND c."
+ groupBy + " = @group" + (union != null ? " AND c.[Union] = '" + union + "'" : "");
using (var cmd = new SqlCeCommand(sql, database_.GetConnection()))
{
cmd.Parameters.AddWithValue("@time", time);
cmd.Parameters.Add("@group", SqlDbType.NVarChar);
double fileTotal = 0;
int row = 2;
foreach (string group in categories_[groupBy])
{
cmd.Parameters[1].Value = group;
SqlCeDataReader reader = cmd.ExecuteReader();
double groupTotal = 0;
while (reader.Read())
{
cells[row, 1] = reader.GetString(0);
cells[row, 2] = Math.Round(reader.GetDouble(1), 2);
groupTotal += cells[row, 2].Value;
++row;
}
reader.Close();
cells.Rows[row].Font.Bold = true;
cells[row, 1] = group;
cells[row, 2] = groupTotal;
fileTotal += groupTotal;
groupTotal = 0;
++row;
}
cells.Rows[row].Font.Bold = true;
cells[row, 1] = "合计";
cells[row, 2] = fileTotal;
}
}
private double getpurchashedprice(string id, SqlCeConnection conn)
{
double pp = -1;
SqlCeDataReader reader = null;
string query = "SELECT purchase_price FROM sub_catagory WHERE ItemId = @id";
SqlCeCommand cmd = new SqlCeCommand(query, conn);
cmd.Parameters.AddWithValue("@id", id);
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
pp = reader.GetDouble(0);
}
}
catch (Exception ex)
{
MessageBox.Show("Error while extracting purchashed price from Database " + ex);
}
return(pp);
}
/// <summary>
/// Charge la liste des cibles de précision du tireur
/// </summary>
private void ChargerCiblePrecision()
{
m_colCiblePrecision.Clear();
using (SqlCeConnection con = new SqlCeConnection(BaseDeDonnee.InfoConnexion))
{
con.Open();
using (SqlCeCommand com = new SqlCeCommand("SELECT * FROM ciblePrecision WHERE idTireur = @idTireur ORDER BY dateTir DESC", con))
{
com.Parameters.AddWithValue("@idTireur", m_id);
SqlCeDataReader objReader = com.ExecuteReader();
while (objReader.Read())
{
int idCible = objReader.GetInt32(0);
int idTireur = objReader.GetInt32(1);
float score = (float)objReader.GetDouble(2);
DateTime dateTir = objReader.GetDateTime(3);
m_colCiblePrecision.Add(new CiblePrecision(idCible, idTireur, score, dateTir));
}
}
}
}
//records
public Tuple <LinkedList <int>, LinkedList <int>, LinkedList <double> > Records(String colName, String criterion)
{
LinkedList <int> scanID = new LinkedList <int>();
LinkedList <int> scanTypeID = new LinkedList <int>();
LinkedList <double> value = new LinkedList <double>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "SELECT * FROM Records WHERE @ColName LIKE @Criterion";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@ColName", colName);
selectQuery.Parameters.Add("@Criterion", criterion);
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
scanID.AddLast(reader.GetInt32(0));
scanTypeID.AddLast(reader.GetInt32(1));
value.AddLast(reader.GetDouble(2));
}
reader.Close();
return(Tuple.Create(scanID, scanTypeID, value));
}
private DailyUsageBO ReadRecord(SqlCeDataReader reader)
{
DailyUsageBO result = new DailyUsageBO();
result.Day = reader.GetDateTime(0);
result.Month = reader.GetString(1);
result.Upload = reader.GetDouble(2);
result.Download = reader.GetDouble(3);
result.Total = reader.GetDouble(4);
if (!reader.IsDBNull(5))
result.Period = new Period(reader.GetString(5));
return result;
}
private void printinvoice_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
{
dboperation operation = new dboperation();
SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath);
SqlCeCommand cmd = null;
byte[] photo_array = null;
SqlCeDataReader reader = null;
MemoryStream ms = new MemoryStream();
Bitmap bitmap = null;
string selectquery = "SELECT Id,companyName, companyAddr, companyMobile, companyEmail, companygstin, companyVAT, companyCST, companyPAN, regkey,logo FROM regdetails WHERE Id=1";
cmd = new SqlCeCommand(selectquery, conn);
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
cname = reader.GetString(1);
caddr = reader.GetString(2);
cmbl = reader.GetString(3);
cemail = reader.GetString(4);
cgstin = reader.GetString(5);
cvat = reader.GetString(6);
ccst = reader.GetString(7);
cpan = reader.GetString(8);
photo_array = (byte[])reader["logo"];
MemoryStream mes = new MemoryStream(photo_array);
bitmap = new Bitmap(mes);
}
}
catch (Exception ex)
{
MessageBox.Show("" + ex);
}
Image logoimg = bitmap;
e.Graphics.DrawString("Invoice", new Font("Arial", 13, FontStyle.Regular), Brushes.Black, new Point(390, 28));
e.Graphics.DrawImage(logoimg, 40, 63, 30, 30);
e.Graphics.DrawLine(new Pen(Color.Black, 3), 29, 57, 795, 57);
e.Graphics.DrawLine(new Pen(Color.Black, 3), 29, 57, 29, 1050);
e.Graphics.DrawLine(new Pen(Color.Black, 3), 795, 57, 795, 1050);
e.Graphics.DrawLine(new Pen(Color.Black, 3), 29, 1050, 795, 1050);
e.Graphics.DrawLine(new Pen(Color.Black, 1), 550, 57, 550, 212);
e.Graphics.DrawLine(new Pen(Color.Black, 1), 550, 90, 795, 90);
e.Graphics.DrawLine(new Pen(Color.Black, 1), 550, 120, 795, 120);
e.Graphics.DrawLine(new Pen(Color.Black, 3), 29, 212, 795, 212);
// Buyer Information segment
e.Graphics.DrawLine(new Pen(Color.Black, 2), 29, 310, 795, 310);
// Item Table drawing
e.Graphics.DrawLine(new Pen(Color.Black, 2), 29, 355, 795, 355);
e.Graphics.DrawLine(new Pen(Color.Black, 2), 55, 310, 55, 880);
e.Graphics.DrawLine(new Pen(Color.Black, 2), 380, 310, 380, 880);
e.Graphics.DrawLine(new Pen(Color.Black, 2), 435, 310, 435, 880);
e.Graphics.DrawLine(new Pen(Color.Black, 2), 510, 310, 510, 880);
e.Graphics.DrawLine(new Pen(Color.Black, 2), 558, 310, 558, 880);
e.Graphics.DrawLine(new Pen(Color.Black, 2), 632, 310, 632, 880);
e.Graphics.DrawLine(new Pen(Color.Black, 2), 698, 310, 698, 880);
// e.Graphics.DrawLine(new Pen(Color.Black, 2), , 250, , 1070);
using (Font font1 = new Font("Arial", 11, FontStyle.Italic, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(280, 1058, 400, 30);
e.Graphics.DrawString("[This is Computer generated document]", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// company Name
using (Font font1 = new Font("Arial", 18, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(77, 65, 490, 32);
e.Graphics.DrawString(cname, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Company Address
using (Font font1 = new Font("Arial", 12, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(35, 100, 490, 40);
e.Graphics.DrawString(caddr, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Company Email
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(35, 145, 400, 30);
e.Graphics.DrawString("Email: " + cemail, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Company Phone Number
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(35, 165, 400, 20);
e.Graphics.DrawString("Contact No. : " + cmbl, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Company VAT TIN
if (cvat != "0")
{
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(35, 185, 400, 25);
e.Graphics.DrawString("Company's VAT TIN - " + cvat, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
}
// Invoice Number
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
string temp = (invoiceprefix + invoicenumber).Replace(" ", string.Empty);
RectangleF rectF1 = new RectangleF(553, 64, 260, 30);
e.Graphics.DrawString("Invoice No.- " + temp, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Invoice Date
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
if (dt == default(DateTime))
{
RectangleF rectF1 = new RectangleF(556, 95, 254, 30);
e.Graphics.DrawString("Dated - " + DateTime.Now.Date.ToString("dd/MM/yyyy"), font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
else
{
RectangleF rectF1 = new RectangleF(556, 95, 254, 30);
e.Graphics.DrawString("Dated - " + dt.ToString("dd/MM/yyyy"), font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
}
// GSTIN number
using (Font font1 = new Font("Arial", 11, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(556, 130, 260, 25);
e.Graphics.DrawString("GSTIN/UIN - " + cgstin, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Company CST Number
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(556, 153, 260, 25);
e.Graphics.DrawString("CST No. - " + ccst, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Company PAN number
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(556, 176, 260, 25);
e.Graphics.DrawString("PAN - " + cpan, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Buyer Information
// Customer Name
e.Graphics.DrawString("Buyer Information", new Font("Arial", 13, FontStyle.Bold), Brushes.Black, new Point(360, 217));
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(35, 247, 400, 20);
e.Graphics.DrawString("Cust. Name - " + custNametb.Text, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Customer Address
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(35, 270, 454, 38);
e.Graphics.DrawString("Cust. Address - " + addrtb.Text, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
e.Graphics.DrawLine(new Pen(Color.Black, 2), 515, 246, 515, 310);
// Customer Contact Number
using (Font font1 = new Font("Arial", 11, FontStyle.Regular, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(525, 260, 265, 40);
e.Graphics.DrawString("Customer Contact Number " + mobiletb.Text, font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Column Name
// Serial No.
using (Font font1 = new Font("Arial", 11, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(29, 318, 30, 35);
e.Graphics.DrawString("SL No.", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Description of Items
using (Font font1 = new Font("Arial", 11, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(120, 318, 200, 35);
e.Graphics.DrawString("Description of Item(s)", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// HSN/SAC
using (Font font1 = new Font("Arial", 11, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(385, 318, 40, 35);
e.Graphics.DrawString("HSN", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Quantity
using (Font font1 = new Font("Arial", 11, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(440, 318, 76, 35);
e.Graphics.DrawString("Quantity", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Measuring Unit
using (Font font1 = new Font("Arial", 11, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(513, 318, 45, 35);
e.Graphics.DrawString("Unit", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Selling Price
using (Font font1 = new Font("Arial", 11, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(568, 318, 47, 35);
e.Graphics.DrawString("Rate/Unit", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Discount Amount
using (Font font1 = new Font("Arial", 10, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(634, 318, 60, 35);
e.Graphics.DrawString("Disc Amount", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
}
// Total Amount Item Basis
using (Font font1 = new Font("Arial", 11, FontStyle.Bold, GraphicsUnit.Point))
{
RectangleF rectF1 = new RectangleF(716, 318, 75, 35);
e.Graphics.DrawString("Amount", font1, Brushes.Black, rectF1);
e.Graphics.DrawRectangle(Pens.Transparent, Rectangle.Round(rectF1));
} // */
// Signature Segment
e.Graphics.DrawString("Authorized Signature", new Font("Arial", 12, FontStyle.Bold), Brushes.Black, new Point(610, 977));
// Print items
Boolean flag = false;
try
{
string item, desc, qty, mu, sp = "0", tdi, sg, cg;
string query = "SELECT selling_price FROM sub_catagory WHERE ItemId=@subcatid";
SqlCeDataReader rdr = null;
double bucket = 0;
for (int j = numberOfItemsPrinted; j < ItemList.RowCount; j++)
{
itemPerPage++;
if (itemPerPage <= 6)
{
numberOfItemsPrinted++;
if (numberOfItemsPrinted <= ItemList.RowCount)
{
item = ItemList.Rows[j].Cells[1].Value.ToString();
desc = ItemList.Rows[j].Cells[2].Value.ToString();
qty = ItemList.Rows[j].Cells[3].Value.ToString();
mu = ItemList.Rows[j].Cells[4].Value.ToString();
tdi = ItemList.Rows[j].Cells[6].Value.ToString();
sg = ItemList.Rows[j].Cells[7].Value.ToString();
cg = ItemList.Rows[j].Cells[8].Value.ToString();
cmd = new SqlCeCommand(query, conn);
if (!searchflag)
{
cmd.Parameters.AddWithValue("@subcatid", ItemList.Rows[j].Cells[0].Value.ToString());
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
sp = rdr.GetDouble(0).ToString();
}
bucket = double.Parse(sp) * float.Parse(qty);
itemrow(e, item, "8247", qty, mu, sp, tdi, bucket.ToString(), desc, sg, cg);
}
else
{
sp = ItemList.Rows[j].Cells[5].Value.ToString();
itemrow(e, item, "8247", qty, mu, sp, tdi, sp, desc, sg, cg);
}
}
else
{
finish_bottom_segment(e);
e.HasMorePages = false;
}
}
else
{
itemPerPage = 0;
e.HasMorePages = true;
flag = true;
}
}
if (flag)
{
more_bottom_segment(e);
spacer = 0;
}
else
{
finish_bottom_segment(e);
numberOfItemsPrinted = 0;
itemPerPage = 0;
ItemCount = 1;
spacer = 0;
}
}
catch (Exception ex)
{
MessageBox.Show("" + ex);
}
}
private int RunDataReader(SqlCeCommand cmd, SqlCeConnection connection, Char colSepChar, bool removeSpaces)
{
cmd.Connection = connection;
SqlCeDataReader rdr = cmd.ExecuteReader();
int rows = 0;
int maxWidth = 256;
string colSep = colSepChar.ToString();
List <Column> headings = new List <Column>();
CreateHeadings(cmd, conn, rdr, maxWidth, headings);
while (rdr.Read())
{
bool doWrite = (rows == 0 && writeHeaders);
if (!doWrite && rows > 0)
{
doWrite = ((rows % headerInterval) == 0);
}
if (doWrite)
{
for (int x = 0; x < rdr.FieldCount; x++)
{
if (removeSpaces)
{
Console.Write(headings[x].Name);
}
else
{
Console.Write(headings[x].Name.PadRight(headings[x].Width));
}
Console.Write(colSep);
}
Console.WriteLine();
for (int x = 0; x < rdr.FieldCount; x++)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (removeSpaces)
{
sb.Append('-', headings[x].Name.Length);
}
else
{
sb.Append('-', headings[x].Width);
}
Console.Write(sb.ToString());
Console.Write(colSep);
}
Console.WriteLine();
}
for (int i = 0; i < rdr.FieldCount; i++)
{
if (!rdr.IsDBNull(i))
{
string value = string.Empty;
string fieldType = rdr.GetDataTypeName(i);
if (fieldType == "Image" || fieldType == "VarBinary" || fieldType == "Binary" || fieldType == "RowVersion")
{
Byte[] buffer = (Byte[])rdr[i];
StringBuilder sb = new StringBuilder();
sb.Append("0x");
for (int y = 0; y < (headings[i].Width - 2) / 2; y++)
{
sb.Append(buffer[y].ToString("X2", CultureInfo.InvariantCulture));
}
value = sb.ToString();
}
else if (fieldType == "DateTime")
{
value = ((DateTime)rdr[i]).ToString("O");
}
else if (fieldType == "Float")
{
value = rdr.GetDouble(i).ToString("R", System.Globalization.CultureInfo.InvariantCulture);
}
else if (fieldType == "Real")
{
value = rdr.GetFloat(i).ToString("R", System.Globalization.CultureInfo.InvariantCulture);
}
else
{
value = Convert.ToString(rdr[i], cultureInfo);
}
if (removeSpaces)
{
Console.Write(value);
}
else if (headings[i].PadLeft)
{
Console.Write(value.PadLeft(headings[i].Width));
}
else
{
Console.Write(value.PadRight(headings[i].Width));
}
}
else
{
if (removeSpaces)
{
Console.Write("NULL");
}
else
{
Console.Write("NULL".PadRight(headings[i].Width));
}
}
if (i < rdr.FieldCount - 1)
{
Console.Write(colSep);
}
}
rows++;
Console.WriteLine();
}
return(rows);
}
private object DataReaderToObject(SqlCeDataReader reader, EntityType entity)
{
object entityObject = null;
switch (entity)
{
case EntityType.Processor:
entityObject = new EntProcessor();
break;
case EntityType.OS:
entityObject = new EntOS();
break;
case EntityType.Bios:
entityObject = new EntBios();
break;
case EntityType.MotherBoard:
entityObject = new EntMotherBoard();
break;
case EntityType.Disk:
entityObject = new EntDisk();
break;
case EntityType.Memory:
entityObject = new EntMemory();
break;
case EntityType.LogicalDrive:
entityObject = new EntLogicalDrive();
break;
case EntityType.CDRom:
entityObject = new EntCDRom();
break;
case EntityType.Video:
entityObject = new EntVideo();
break;
case EntityType.Multimedia:
entityObject = new EntMultimedia();
break;
case EntityType.Monitor:
entityObject = new EntMonitor();
break;
case EntityType.Share:
entityObject = new EntShare();
break;
case EntityType.StartUp:
entityObject = new EntStartUp();
break;
case EntityType.Hotfix:
entityObject = new EntHotfixes();
break;
case EntityType.Processes:
entityObject = new EntProcesses();
break;
case EntityType.Softwares:
entityObject = new EntSoftwares();
break;
case EntityType.Services:
entityObject = new EntServices();
break;
case EntityType.IPRoutes:
entityObject = new EntIPRoutes();
break;
case EntityType.EnvironmentVar:
entityObject = new EntEnvironmentVars();
break;
case EntityType.Computer:
entityObject = new EntComputer();
break;
case EntityType.Printer:
entityObject = new EntPrinter();
break;
case EntityType.UserGroup:
entityObject = new EntUserGroups();
break;
case EntityType.NetworkAdapter:
entityObject = new EntNetworkAdapter();
break;
}
Type t = entityObject.GetType();
PropertyInfo[] pi = t.GetProperties();
foreach (PropertyInfo prop in pi)
{
if (prop.Name == "ClassName" || prop.Name == "Icon" || prop.Name == "NodeName")
continue;
switch (prop.PropertyType.Name)
{
case "String":
string strValue = reader.GetString(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, strValue, null);
break;
case "Int32":
int intValue = reader.GetInt32(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, intValue, null);
break;
case "Double":
double dValue = reader.GetDouble(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, dValue, null);
break;
case "DateTime":
DateTime dtValue = DateTime.MinValue;
if ( reader.GetValue(reader.GetOrdinal(prop.Name))== DBNull.Value)
{
dtValue = DateTime.MinValue;
}
else
{
dtValue = reader.GetDateTime(reader.GetOrdinal(prop.Name));
}
prop.SetValue(entityObject, dtValue, null);
break;
case "Boolean":
bool bValue = reader.GetBoolean(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, bValue, null);
break;
}
}
return entityObject;
}
private void searchinvoicebtn_Click(object sender, EventArgs e)
{
FinalInvoice invoice = new FinalInvoice();
// Disabling the controls of FinalInvoice
invoice.custNametb.Enabled = false;
invoice.addrtb.Enabled = false;
invoice.mobiletb.Enabled = false;
invoice.paymenttypecb.Enabled = false;
invoice.paymentDetailstb.Enabled = false;
invoice.finalsgsttb.Enabled = false;
invoice.finalcgsttb.Enabled = false;
dboperation operation = new dboperation();
SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath);
string query1 = "SELECT InvoiceNo, customerName, custAddress, custMobile, paymentDetails, totalSGST, totalCGST, totalDiscount, percentSGST, percentCGST, InvoiceTime FROM customerDetails WHERE InvoiceNo=@invoiceno"; // DATEPART(day,InvoiceTime), DATEPART(month,InvoiceTime), DATEPART(year,InvoiceTime)
string query2 = "SELECT InvoiceNo,ItemName,quantity,TotalSellingPrice,TotalPurchasedPrice,perItemSGST,perItemCGST,discountPerItemType,measuringUnit,description FROM invoiceDetails WHERE InvoiceNo=@invoiceno";
// 0 1 2 3 4 5 6 7 8 9
string invoiceresult = "";
SqlCeCommand cmd1 = null;
SqlCeCommand cmd2 = null;
SqlCeDataReader reader = null;
try
{
cmd1 = new SqlCeCommand(query1, conn);
cmd1.Parameters.AddWithValue("@invoiceno", invoicenotb.Text);
reader = cmd1.ExecuteReader();
if (reader.Read())
{
string temp;
string[] separator;
char[] tag = new char[] { '^' };
invoiceresult = reader.GetInt64(0).ToString();
invoice.invoicenumber = invoiceresult;
invoice.custNametb.Text = reader.GetString(1);
invoice.addrtb.Text = reader.GetString(2);
invoice.mobiletb.Text = reader.GetString(3);
temp = reader.GetString(4);
separator = temp.Split(tag);
invoice.paymenttypecb.Text = separator[0];
if (separator.Length > 1)
{
invoice.paymentDetailstb.Text = separator[1];
}
else
{
invoice.paymentDetailstb.Text = "";
}
invoice.finalsgsttb.Text = reader.GetString(8);
invoice.finalcgsttb.Text = reader.GetString(9);
invoice.dt = reader.GetDateTime(10);//Convert.ToDateTime(reader.GetInt32(10).ToString() + "/" + reader.GetInt32(11).ToString() + "/" + reader.GetInt32(12));
}
else
{
MessageBox.Show("Invoice No: " + invoicenotb.Text + " not exist in the Database");
}
if (invoiceresult != "")
{
int count = 0;
cmd2 = new SqlCeCommand(query2, conn);
SqlCeDataReader read = null;
cmd2.Parameters.AddWithValue("@invoiceno", invoiceresult);
read = cmd2.ExecuteReader();
while (read.Read())
{
invoice.ItemList.Rows.Add();
invoice.ItemList.Rows[count].Cells[1].Value = read.GetString(1); // Item Name
invoice.ItemList.Rows[count].Cells[2].Value = read.GetString(9); // Description
invoice.ItemList.Rows[count].Cells[3].Value = read.GetString(2); // Quantity
invoice.ItemList.Rows[count].Cells[4].Value = read.GetString(8); // Measuring Unit
invoice.ItemList.Rows[count].Cells[5].Value = read.GetString(3); // Total Selling Price
invoice.ItemList.Rows[count].Cells[6].Value = read.GetString(7); // Total Discount per item
invoice.ItemList.Rows[count].Cells[7].Value = read.GetDouble(5); // percent SGST
invoice.ItemList.Rows[count].Cells[8].Value = read.GetDouble(6); // percent CGST
count++;
}
invoice.searchflag = true;
invoice.ShowDialog();
}
}
catch (Exception ex)
{
MessageBox.Show("" + ex);
}
}
//analyze SQL data
public void GetAvg()
{
try
{
using (SqlCeConnection con = new SqlCeConnection(conString))
{
con.Open();
using (SqlCeCommand com = new SqlCeCommand("SELECT AVG(PID) FROM table1 WHERE Equip = @equip", con))
{
com.Parameters.AddWithValue("@equip", MainWindow.Eqiup);
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
int numb5 = reader.GetInt32(0);
MainWindow.EnteredPID = numb5;
}
}
reader.Close();
}
using (SqlCeCommand com1 = new SqlCeCommand("SELECT AVG(SlopeDWN) FROM table1 WHERE Equip = @equip", con))
{
com1.Parameters.AddWithValue("@equip", MainWindow.Eqiup);
SqlCeDataReader reader1 = com1.ExecuteReader();
while (reader1.Read())
{
if (!reader1.IsDBNull(0))
{
MainWindow main = new MainWindow();
double numb5 = reader1.GetDouble(0);
double numb5Rnd = Math.Round(numb5, 5);
main.TB3 = numb5Rnd.ToString();
// textBox3.Text = numb5Rnd.ToString();
MainWindow.EnteredSlopeDWN = numb5Rnd;
}
}
reader1.Close();
}
using (SqlCeCommand com2 = new SqlCeCommand("SELECT AVG(SlopeUP) FROM table1 WHERE Equip = @equip", con))
{
com2.Parameters.AddWithValue("@equip", MainWindow.Eqiup);
SqlCeDataReader reader2 = com2.ExecuteReader();
while (reader2.Read())
{
if (!reader2.IsDBNull(0))
{
double numb5 = reader2.GetDouble(0);
double numb5Rnd = Math.Round(numb5, 5);
MainWindow m = new MainWindow();
m.TB10 = numb5Rnd.ToString();
// textBox10.Text = numb5Rnd.ToString();
MainWindow.EnteredSlopeUP = numb5Rnd;
}
}
reader2.Close();
}
using (SqlCeCommand com3 = new SqlCeCommand("SELECT AVG(BestHFR) FROM table1 WHERE Equip = @equip", con))
{
com3.Parameters.AddWithValue("@equip", MainWindow.Eqiup);
SqlCeDataReader reader3 = com3.ExecuteReader();
while (reader3.Read())
{
if (!reader3.IsDBNull(0))
{
int numb6 = reader3.GetInt32(0);
MainWindow m = new MainWindow();
m.TB15 = numb6.ToString();
// textBox15.Text = numb6.ToString();
}
}
reader3.Close();
}
/*
* // rem'd ****moved to mainWindow load so it puts focus position value in at startup
* using (SqlCeCommand com4 = new SqlCeCommand("SELECT AVG(FocusPos) FROM table1 WHERE Equip = @equip", con))
* {
* com4.Parameters.AddWithValue("@equip", equip);
* SqlCeDataReader reader4 = com4.ExecuteReader();
* while (reader4.Read())
* {
* if (!reader4.IsDBNull(0))
* {
* int numb7 = reader4.GetInt32(0);
* // textBox4.Text = numb7.ToString();******88888rem'd 4-10
* }
* }
* reader4.Close();
* }
*
*/
con.Close();
}
}
catch (Exception ex)
{
MainWindow m = new MainWindow();
m.Log("GetAvg Error" + ex.ToString());
}
}
public Tuple <LinkedList <int>, LinkedList <double[]>, LinkedList <double[]>, LinkedList <String[]>, LinkedList <String[]> > limbCoordinates(int ScanID)
{
LinkedList <int> scanID = new LinkedList <int>();
double[] d = new double[3];
LinkedList <double[]> joint1 = new LinkedList <double[]>();
LinkedList <double[]> joint2 = new LinkedList <double[]>();
String[] s = new String[3];
LinkedList <String[]> joint3 = new LinkedList <String[]>();
LinkedList <String[]> joint4 = new LinkedList <String[]>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "Select * from limbCoordinates where scanID = @ScanID";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@ScanID", ScanID);
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
scanID.AddLast(reader.GetInt32(0));
d[0] = reader.GetDouble(1);
d[1] = reader.GetDouble(2);
d[2] = reader.GetDouble(3);
joint1.AddLast(d);
d[0] = reader.GetDouble(4);
d[1] = reader.GetDouble(5);
d[2] = reader.GetDouble(6);
joint2.AddLast(d);
System.Type type = reader.GetFieldType(4);
if (Type.GetTypeCode(type) == TypeCode.String)
{
s[0] = reader.GetString(7);
s[1] = reader.GetString(8);
s[2] = reader.GetString(9);
joint3.AddLast(s);
}
else if (Type.GetTypeCode(type) == TypeCode.Double)
{
s[0] = reader.GetDouble(7).ToString();
s[1] = reader.GetDouble(8).ToString();
s[2] = reader.GetDouble(9).ToString();
joint3.AddLast(s);
}
type = reader.GetFieldType(10);
if (Type.GetTypeCode(type) == TypeCode.String)
{
s[0] = reader.GetString(10);
s[1] = reader.GetString(11);
s[2] = reader.GetString(12);
joint3.AddLast(s);
}
else if (Type.GetTypeCode(type) == TypeCode.Double)
{
s[0] = reader.GetDouble(10).ToString();
s[1] = reader.GetDouble(11).ToString();
s[2] = reader.GetDouble(12).ToString();
joint3.AddLast(s);
}
}
reader.Close();
return(Tuple.Create(scanID, joint1, joint2, joint3, joint4));
}
