public POReturnItemDetails Details(long DebitMemoItemID)
{
try
{
string SQL = SQLSelect() + "WHERE DebitMemoItemID = @DebitMemoItemID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmPOReturnItemID = new MySqlParameter("@DebitMemoItemID", MySqlDbType.Int64);
prmPOReturnItemID.Value = DebitMemoItemID;
cmd.Parameters.Add(prmPOReturnItemID);
MySqlDataReader myReader = base.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
POReturnItemDetails Details = new POReturnItemDetails();
while (myReader.Read())
{
Details.DebitMemoItemID = DebitMemoItemID;
Details.DebitMemoID = myReader.GetInt64("DebitMemoID");
Details.ProductID = myReader.GetInt64("ProductID");
Details.ProductCode = "" + myReader["ProductCode"].ToString();
Details.BarCode = "" + myReader["BarCode"].ToString();
Details.Description = "" + myReader["Description"].ToString();
Details.ProductUnitID = myReader.GetInt16("ProductUnitID");
Details.ProductUnitCode = "" + myReader["ProductUnitCode"].ToString();
Details.Quantity = myReader.GetDecimal("Quantity");
Details.UnitCost = myReader.GetDecimal("UnitCost");
Details.Discount = myReader.GetDecimal("Discount");
Details.DiscountApplied = myReader.GetDecimal("DiscountApplied");
Details.DiscountType = (DiscountTypes)Enum.Parse(typeof(DiscountTypes), myReader.GetString("DiscountType"));
Details.Amount = myReader.GetDecimal("Amount");
Details.VAT = myReader.GetDecimal("VAT");
Details.VatableAmount = myReader.GetDecimal("VatableAmount");
Details.EVAT = myReader.GetDecimal("EVAT");
Details.EVatableAmount = myReader.GetDecimal("EVatableAmount");
Details.LocalTax = myReader.GetDecimal("LocalTax");
Details.isVATInclusive = myReader.GetBoolean("isVATInclusive");
Details.VariationMatrixID = myReader.GetInt64("VariationMatrixID");
Details.MatrixDescription = "" + myReader["MatrixDescription"].ToString();
Details.ProductGroup = "" + myReader["ProductGroup"].ToString();
Details.ProductSubGroup = "" + myReader["ProductSubGroup"].ToString();
Details.ItemStatus = (POReturnItemStatus)Enum.Parse(typeof(POReturnItemStatus), myReader.GetString("ItemStatus"));
if (myReader["IsVatable"].ToString() == "1")
{
Details.IsVatable = true;
}
Details.Remarks = "" + myReader["Remarks"].ToString();
}
myReader.Close();
return(Details);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private void okBtn_Click_1(object sender, EventArgs e)
{
indx = driveListView.SelectedIndices[0];
if (allDrives[indx].IsReady)
{
// Disable other UI
startBtn.Enabled = false;
closeBtn.Enabled = false;
refreshBtn.Enabled = false;
try
{
// Create a sql command
sqlCmd = new MySqlCommand();
sqlCmd.Connection = Program.dbConnection;
// Get the tco value from table counter
sqlCmd.CommandText = "SELECT tco FROM counter";
MySqlDataReader sqlReader = sqlCmd.ExecuteReader();
sqlReader.Read();
tco = sqlReader.GetInt16(0);
sqlReader.Close();
// Increment the tco
++tco;
// Prepare the progress bar
catalogingProg.Value = 0;
catalogingProg.Maximum = 100;
totSize = (allDrives[indx].TotalSize - allDrives[indx].TotalFreeSpace);
accSize = 0;
// Prepare archive information
archiveInfo.totDirs = 0;
archiveInfo.totFiles = 0;
archiveInfo.accessDenied = 0;
// Initialize stopFlag to false (Don't stop archiving)
stopFlag = false;
// Prepare the insert sql query for files_info table
cmdStr = "INSERT INTO files_info (storage_id, path, name, extension, size, attributes, creation_date, creation_time, modification_date, modification_time) VALUES(" + tco + ", ";
path = allDrives[indx].RootDirectory.ToString();
//path = "D:\\ebooks";
// Initialize a new thread for crawling files of the specified storage unit
crawler = new Thread(this.findAllFiles);
crawler.Start();
// Record the start time
sto = DateTime.Now;
// Update the form UI
while (crawler.IsAlive)
{
totDirsTxt.Text = archiveInfo.totDirs.ToString();
totFilesTxt.Text = archiveInfo.totFiles.ToString();
catalogingProg.Value = Convert.ToInt32(accSize * 100 / totSize);
Application.DoEvents();
}
// Record the stop time
eto = DateTime.Now;
// Save the storage unit info in to table main
cmdStr = "INSERT INTO main (storage_id, storage_type, label, file_system_type, total_size, used_size, total_dirs_archived, total_files_archived) Values(" + tco + ", ";
cmdStr += "'" + allDrives[indx].DriveType + "', ";
cmdStr += "'" + labelTxt.Text + "', ";
cmdStr += "'" + allDrives[indx].DriveFormat + "', ";
cmdStr += allDrives[indx].TotalSize + ", ";
cmdStr += (allDrives[indx].TotalSize - allDrives[indx].TotalFreeSpace) + ", ";
cmdStr += archiveInfo.totDirs + ", ";
cmdStr += archiveInfo.totFiles;
cmdStr += ")";
sqlCmd.CommandText = cmdStr;
sqlCmd.ExecuteNonQuery();
// Update the table counter tco value
sqlCmd.CommandText = "UPDATE counter SET tco=" + tco;
sqlCmd.ExecuteNonQuery();
// Display the results including
// time consumed in the process
// number of archived directories
// number of archived files
// number of access denied errors
// Note: change this msgboxes to form for displaying results, and could be on same form by using
// a hidden controls
TimeSpan ts = eto.Subtract(sto);
MessageBox.Show("Finished Successfult \n- Time consumed is-> M: " + ts.Minutes + " S: " + ts.Seconds + " MS: " + ts.Milliseconds);
MessageBox.Show("Number of archived directories: " + archiveInfo.totDirs + " Number of archived files: " + archiveInfo.totFiles + " Number of access-denied: " + archiveInfo.accessDenied);
// Reset the progress bar
catalogingProg.Value = 0;
}
catch (InvalidOperationException ex)
{
MessageBox.Show(ex.Message + " - " + tmpStr);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message + " - " + tmpStr);
}
// Enable other UI
startBtn.Enabled = true;
closeBtn.Enabled = true;
refreshBtn.Enabled = true;
}
else
{
MessageBox.Show("Drive Not Ready");
}
}
private Event getEventRow(int proj_id, int sch_no, int event_id)
{
Event evnt = new Event();
try
{
MySqlDataReader reader = DBConnection.getData("select e.event_id, e.proj_id, e.visit_type_id, e.to_date_time, e.from_date_time, e.sch_no, e.feedback, e.Other, e.to_do_list, e.resource, e.check_list, e.travelling_mode, e.accommodation_mode, e.meals, p.client_id " +
" from event e, project p " +
" where e.proj_id = p.proj_id and e.event_id = " + event_id + " and e.proj_id = " + proj_id + " and e.sch_no = " + sch_no + ";");
if (reader.Read())
{
evnt.EventId = event_id;
evnt.EventProject = new Project(proj_id, reader.GetInt16("client_id"));
evnt.ScheduleId = new Schedule(sch_no);
evnt.Type = new EventType(reader.GetInt16("visit_type_id"));
evnt.From = reader.GetDateTime("from_date_time");
evnt.To = reader.GetDateTime("to_date_time");
evnt.Feedback = reader.GetString("feedback");
evnt.Other = reader.GetString("other");
evnt.TodoList = reader.GetString("to_do_list");
//evnt.Resource = reader.GetString("resource");
evnt.Checklist = reader.GetString("check_list");
evnt.TravelMode = reader.GetString("travelling_mode");
evnt.AccommodationMode = reader.GetString("accommodation_mode");
evnt.Meals = reader.GetString("meals");
reader.Close();
MySqlDataReader reader1 = DBConnection.getData("select s.staff_id, s.first_name, s.last_name, et.feedback, ett.task " +
"from event_technicians et, event_technician_task ett, staff s " +
"where (ett.event_tech_id = et.event_staff_id) and (s.staff_id = et.staff_id) and et.event_id = " + event_id + " and et.proj_id = " + proj_id + " and et.sch_no = " + sch_no + ";");
ArrayList serEng = new ArrayList();
while (reader1.Read())
{
EventTechnician et = new EventTechnician();
et.Technician = new Staff(reader1.GetInt16("staff_id"), reader1.GetString("first_name"), reader1.GetString("last_name"));
et.Task = reader1.GetString("task");
et.Feedback = reader1.GetString("feedback");
serEng.Add(et);
}
evnt.ServEngineer = serEng;
reader1.Close();
ArrayList resoArray = new ArrayList();
MySqlDataReader reader3 = DBConnection.getData("select sr.resource_id, sr.qty, r.name from event_resources sr, resource r where (sr.event_id = " + event_id + " and sr.sch_no =" + sch_no + " and sr.proj_id = " + proj_id + ") and (sr.resource_id = r.resource_id);");
while (reader3.Read())
{
Resource reso = new Resource();
reso.ResourceId = int.Parse(reader3.GetString("resource_id"));
reso.Name = reader3.GetString("name");
reso.TotalQty = int.Parse(reader3.GetString("qty"));
resoArray.Add(reso);
}
evnt.ResoArray = resoArray;
reader3.Close();
}
else
{
evnt = null;
}
}
catch (Exception)
{
MessageBox.Show("Something went wrong!");
throw;
}
return(evnt);
}
public Usuario buscarXId(int idUsuario, ref string mensaje)
{
Usuario usuario = null;
MySqlCommand cmd = null;
MySqlDataReader data = null;
try
{
cmd = this.conf.EjecutarSQL("usuario_consultar");
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?opcion", 2);
cmd.Parameters["?opcion"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?id_usuarioi", idUsuario);
cmd.Parameters["?id_usuarioi"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?nombrei", string.Empty);
cmd.Parameters["?nombrei"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?cedulai", string.Empty);
cmd.Parameters["?cedulai"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?idRoli", -1);
cmd.Parameters["?idRoli"].Direction = ParameterDirection.Input;
data = cmd.ExecuteReader();
if (data.Read())
{
usuario = new Usuario();
usuario.Idtbl_usuario = (data.IsDBNull(data.GetOrdinal("id")))?0:data.GetInt16("id");
usuario.Id_Rol = (data.IsDBNull(data.GetOrdinal("idRol"))) ? 0 : data.GetInt16("idRol");
usuario.Cedula_Empleado = (data.IsDBNull(data.GetOrdinal("cedula"))) ? "" : data.GetString("cedula");
usuario.Nombre_Empleado = (data.IsDBNull(data.GetOrdinal("nombre"))) ? "" : data.GetString("nombre");
usuario.User = (data.IsDBNull(data.GetOrdinal("usuario"))) ? "" : data.GetString("usuario");
usuario.Password = (data.IsDBNull(data.GetOrdinal("password"))) ? "" : data.GetString("password");
usuario.Estado = (data.IsDBNull(data.GetOrdinal("estado"))) ? false: data.GetBoolean("estado");
}
}
catch (Exception e) { mensaje = e.Message; }
finally
{
if (data != null)
{
data.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
this.conf.cerrar();
}
return(usuario);
}
private void button1_Click(object sender, EventArgs e)
{
string constr = "";
if (textBox1.Text.Equals(""))
{
MessageBox.Show("Plesae enter Login ID;");
textBox1.Focus();
return;
}
if (textBox2.Text.Equals(""))
{
MessageBox.Show("Plesae enter Password;");
textBox2.Focus();
return;
}
int pass = 0;
constr = constring();
if (constr.Equals(""))
{
return;
}
MySqlConnection con = new MySqlConnection(constr);
string query = @"SELECT COUNT(*) AS cnt,u.id, pu.port_id,p.port_name,p.port_alias,
p.Operator_description,p.port_add1
FROM users AS u
JOIN port_user AS pu ON pu.user_id=u.id
JOIN ports AS p ON p.id = pu.port_id
WHERE u.username='******' AND u.password1=SHA1('" + textBox2.Text.Trim() + "') ";
MySqlCommand cmd = new MySqlCommand(query, con);
con.Open();
MySqlDataReader mred = cmd.ExecuteReader();
if (mred.Read())
{
//pass = mred.GetString("cnt");
pass = mred.GetInt16("cnt");
if (pass == 1)
{
//utility.UserId = mred.GetUInt16("id");
//utility.UserName = textBox1.Text;
//utility.PortId= mred.GetString("port_id");
//utility.PortName = mred.GetString("port_name");
//utility.PortAdress= mred.GetString("port_add1");
//MessageBox.Show(utility.UserName);
ReportViewForm rvForm = new ReportViewForm();
rvForm.username = textBox1.Text;
usr = textBox1.Text;
int userId = mred.GetUInt16("id");
WeighbridgeEntry frm = new WeighbridgeEntry();
this.Hide();
frm.userName = textBox1.Text;
frm.userID = userId;
frm.portName = mred.GetString("port_name");
frm.portId = mred.GetString("port_id");
frm.portAdress = mred.GetString("port_add1");
frm.constr = constr;
frm.Show();// this.Dispose();
}
else
{
MessageBox.Show("Wrong Password;");
textBox2.Focus();
con.Close();
return;
}
}
else
{
MessageBox.Show("Plesae enter Correct Login ID;");
textBox1.Focus();
con.Close();
return;
}
con.Close();
}
public void project()
{
MySqlConnection conn = new MySqlConnection(_connString);
MySqlCommand cmd = new MySqlCommand();
string sql_count = " select" +
" sum(a.count)" +
" from" +
" ( select count(*) as count from project union all " +
" select count(*) as count from project_category union all " +
" select count(*) as count from project_task ) a ";
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql_count;
cmd.CommandType = CommandType.Text;
int count = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
int value = 0;
Dispatcher.BeginInvoke((Action)(() => projectMaximum.Text = count.ToString()));
Dispatcher.BeginInvoke((Action)(() => projectValue.Text = value.ToString()));
Dispatcher.BeginInvoke((Action)(() => progProject.Maximum = count));
Dispatcher.BeginInvoke((Action)(() => progProject.Value = value));
string sql_proj_category = " SELECT * FROM project_category;";
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql_proj_category;
cmd.CommandType = CommandType.Text;
MySqlDataReader category_reader = cmd.ExecuteReader();
while (category_reader.Read())
{
using (db db = new db())
{
project_template project_template = new project_template();
project_template.name = category_reader.GetString("category");
project_template.is_active = true;
db.project_template.Add(project_template);
db.SaveChanges();
value += 1;
Dispatcher.BeginInvoke((Action)(() => progProject.Value = value));
Dispatcher.BeginInvoke((Action)(() => projectValue.Text = value.ToString()));
}
}
conn.Close();
string sql_template = " SELECT project_template.*, project_category.category, item_sku.product FROM project_template "
+ " INNER JOIN project_category ON project_template.id_category = project_category.id"
+ " INNER JOIN item_sku ON project_template.id_sku = item_sku.id;";
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql_template;
cmd.CommandType = CommandType.Text;
MySqlDataReader template_reader = cmd.ExecuteReader();
while (template_reader.Read())
{
using (db db = new db())
{
project_template_detail project_template_detail = new project_template_detail();
string category = template_reader.GetString("category");
project_template_detail.id_project_template = db.project_template.Where(i => i.name == category).FirstOrDefault().id_project_template;
project_template_detail.code = template_reader.GetString("task_number");
project_template_detail.item_description = template_reader.GetString("product");
if (db.items.Where(i => i.name == project_template_detail.item_description).FirstOrDefault() != null)
{
project_template_detail.id_item = db.items.Where(i => i.name == project_template_detail.item_description).FirstOrDefault().id_item;
if (template_reader.GetInt16("id_task_rel") == 0)
{
//no parent. do nothing
}
else
{
//has parent
string code = template_reader.GetString("task_number");
int pos = code.LastIndexOf('.');
if (pos >= 0)
{
code = code.Substring(0, pos);
}
project_template_detail.parent = db.project_template_detail.Where(p => p.code == code &&
p.id_project_template == project_template_detail.id_project_template)
.FirstOrDefault();
}
//Insert into Context & Save
db.project_template_detail.Add(project_template_detail);
db.SaveChanges();
}
}
}
conn.Close();
string sql_proj = " SELECT * FROM project";
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql_proj;
cmd.CommandType = CommandType.Text;
MySqlDataReader proj_reader = cmd.ExecuteReader();
while (proj_reader.Read())
{
using (db db = new db())
{
db.Configuration.AutoDetectChangesEnabled = false;
project project = new project();
project.id_project = proj_reader.GetInt32("id");
project.est_start_date = proj_reader.GetDateTime("begin_date");
if (proj_reader["id_contact"] is DBNull)
{
value += 1;
continue;
}
else
{
project.id_contact = proj_reader.GetInt32("id_contact");
}
project.name = proj_reader.GetString("project");
if (project.Error == null)
{
db.projects.Add(project);
db.SaveChanges();
value += 1;
Dispatcher.BeginInvoke((Action)(() => progProject.Value = value));
Dispatcher.BeginInvoke((Action)(() => projectValue.Text = value.ToString()));
}
}
}
conn.Close();
string sql_proj_task = " SELECT project_task.*, project.project, item_sku.product,app_status.status FROM project_task "
+ " INNER JOIN project ON project_task.id_project = project.id"
+ " INNER JOIN item_sku ON project_task.id_sku = item_sku.id"
+ " INNER JOIN app_status ON project_task.id_status = app_status.id";
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql_proj_task;
cmd.CommandType = CommandType.Text;
MySqlDataReader proj_task_reader = cmd.ExecuteReader();
while (proj_task_reader.Read())
{
using (db db = new db())
{
project_task project_task = new project_task();
string project = proj_task_reader.GetString("project");
project_task.id_project = db.projects.Where(i => i.name == project).FirstOrDefault().id_project;
project_task.code = proj_task_reader.GetString("task_number");
string product = proj_task_reader.GetString("product");
if (db.items.Where(i => i.name == product).FirstOrDefault() != null)
{
project_task.item_description = db.items.Where(i => i.name == product).FirstOrDefault().name;
project_task.id_item = db.items.Where(i => i.name == product).FirstOrDefault().id_item;
if (proj_task_reader.GetString("status") == "Aprobado")
{
project_task.status = Status.Project.Approved;
}
else if (proj_task_reader.GetString("status") == "Pendiente")
{
project_task.status = Status.Project.Pending;
}
if (!(proj_task_reader["qty"] is DBNull))
{
project_task.quantity_est = proj_task_reader.GetInt16("qty");
}
if (proj_task_reader.GetInt16("id_task_rel") == 0)
{
//no parent. do nothing
}
else
{
//has parent
string code = proj_task_reader.GetString("task_number");
//logic to have children
if (project_task.quantity_est == null)
{
var pos = code.LastIndexOf('.');
if (pos >= 0)
{
code = code.Substring(0, pos);
project_task.parent = db.project_task.Where(p => p.code == code &&
p.id_project == project_task.id_project
).FirstOrDefault();
}
}
else
{
project_task.parent = db.project_task.Where(p => p.code == code &&
p.id_project == project_task.id_project
).FirstOrDefault();
}
}
//if (project_task.Error == null)
//{
db.project_task.Add(project_task);
//}
}
else
{
}
string sql_task = "SELECT * FROM project_task_dimension inner join app_dimension on app_dimension.id=project_task_dimension.id_dimension inner join project_task on project_task.id=project_task_dimension.id_task where id_task=" + proj_task_reader.GetInt32("id");
try
{
MySqlConnection conntask = new MySqlConnection(_connString);
MySqlCommand cmdtask = new MySqlCommand();
conntask.Open();
cmdtask.Connection = conntask;
cmdtask.CommandText = sql_task;
cmdtask.CommandType = CommandType.Text;
MySqlDataReader task_reader = cmdtask.ExecuteReader();
while (task_reader.Read())
{
project_task_dimension project_task_dimension = new project_task_dimension();
string name = task_reader.GetString("dimension");
if (db.app_dimension.Where(x => x.name == name).FirstOrDefault() != null)
{
project_task_dimension.id_dimension = db.app_dimension.Where(x => x.name == name).FirstOrDefault().id_dimension;
project_task_dimension.project_task = project_task;
project_task_dimension.value = task_reader.GetInt32("value");
project_task_dimension.id_measurement = db.app_measurement.FirstOrDefault().id_measurement;
if (project_task_dimension.Error == null)
{
db.project_task_dimension.Add(project_task_dimension);
}
}
}
task_reader.Close();
cmdtask.Dispose();
conntask.Close();
}
catch (Exception ex)
{ throw ex; }
IEnumerable <DbEntityValidationResult> validationresult = db.GetValidationErrors();
if (validationresult.Count() == 0)
{
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
value += 1;
Dispatcher.BeginInvoke((Action)(() => progProject.Value = value));
Dispatcher.BeginInvoke((Action)(() => projectValue.Text = value.ToString()));
}
}
}
cmd.Dispose();
conn.Close();
}
// to change name to button
public details(String str)
{
InitializeComponent();
// new name to button
button2.Text = "Click to Buy More";
//MessageBox.Show("sdfdsf"); to verify working of constructor
String connString = "Server=" + variables.server + ";Port=" + variables.port + ";Database=" + variables.database + ";Uid=" + variables.user + ";password="******";";
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand command = conn.CreateCommand();
// FEtching consumed quantities of user
string tt = variables.buy;
string[] pur = tt.Split('~');
try
{
conn.Open();
}
catch (Exception ex)
{
MessageBox.Show("Connection Problem. Please check the details of database");
}
// FOR RICE
command.CommandText = " Select * from items where item = 'rice'";
MySqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
textBox4.Text = reader.GetInt16("Quota").ToString();
textBox5.Text = pur[0];
textBox6.Text = reader.GetString(reader.GetOrdinal("Unit"));
textBox7.Text = reader.GetInt16("Price").ToString();
// variables.quota += textBox4.Text + "~";
}
textBox3.Text = "" + variables.ricebuy;
r = int.Parse(textBox3.Text) * int.Parse(textBox7.Text);
textBox22.Text = "Rs. " + (r);
reader.Close();
// FOR Wheat
command.CommandText = " Select * from items where item = 'wheat'";
MySqlDataReader reader1 = command.ExecuteReader();
if (reader1.Read())
{
textBox9.Text = reader1.GetInt16("Quota").ToString();
textBox10.Text = pur[1];
textBox11.Text = reader1.GetString(reader1.GetOrdinal("Unit"));
textBox12.Text = reader1.GetInt16("Price").ToString();
//variables.quota += textBox9.Text + "~";
}
textBox18.Text = "" + variables.wheatbuy;
w = int.Parse(textBox18.Text) * int.Parse(textBox12.Text);
textBox21.Text = "Rs. " + (w);
reader1.Close();
// FOR Kesoscen
command.CommandText = " Select * from items where item = 'kerosene'";
MySqlDataReader reader2 = command.ExecuteReader();
if (reader2.Read())
{
textBox14.Text = reader2.GetInt16("Quota").ToString();
textBox15.Text = pur[2];
textBox16.Text = reader2.GetString(reader2.GetOrdinal("Unit"));
textBox17.Text = reader2.GetInt16("Price").ToString();
//variables.quota += textBox14.Text;
}
textBox20.Text = "" + variables.kerosenebuy;
k = int.Parse(textBox17.Text) * int.Parse(textBox20.Text);
textBox23.Text = "Rs. " + (k);
reader2.Close();
textBox19.Text = "Rs. " + (r + k + w);
textBox2.Text = variables.uname;
textBox1.Text = variables.rationno;
//conn.Close();
//estimating the weights purchased from purchase textboxes
variables.ricew = int.Parse(textBox3.Text);
variables.wheatw = int.Parse(textBox18.Text);
variables.kerosenew = int.Parse(textBox20.Text);
}
public void CreateReceipt(object sender, System.Drawing.Printing.PrintPageEventArgs e)
{
con.Open();
MySqlCommand com = new MySqlCommand("SELECT sales_id AS 'ID', drug_name AS 'DRUG NAME',quantity AS 'QUANTITY',price AS 'PRICE', pfno AS 'SERVED BY',sales_date AS 'DATE SOLD',unit AS 'UNITS', serial AS 'RECEIPT NUMBER' FROM sales ORDER BY sales_date ASC", con);
MySqlDataReader results = com.ExecuteReader();
//create a new instance of drugprint class
printSales newSalesPrint = new printSales();
//print the report
Graphics graphic = e.Graphics;
System.Drawing.Font font = new System.Drawing.Font("Courier New", 12); //must use a mono spaced font as the spaces need to line up
float fontHeight = font.GetHeight();
int startX = 10;
int startY = 10;
int offset = 150;
System.Drawing.Image headerImage = System.Drawing.Image.FromFile("C:\\PMS\\Resources\\faith2.png");
graphic.DrawImage(headerImage, startX, startY, 830, 150);
string headerText = "Sales Report".PadLeft(35);
graphic.DrawString(headerText, new System.Drawing.Font("Courier New", 16), new SolidBrush(Color.Black), startX, startY + offset);
offset = offset + (int)fontHeight; //make the spacing consistent
graphic.DrawString("----------------------------------".PadLeft(55), font, new SolidBrush(Color.Black), startX, startY + offset);
offset = offset + (int)fontHeight + 7; //make the spacing consistent
graphic.DrawString("Drug Name |".PadRight(5) + " Qty |".PadRight(5) + " Price |".PadRight(5) + " Served By |".PadRight(5) + " Units |".PadRight(5) + " Receipt No. |".PadRight(5) + " Date".PadRight(5), font, new SolidBrush(Color.Black), startX, startY + offset);
offset = offset + (int)fontHeight + 5; //make the spacing consistent
graphic.DrawString("--------------------------------------------------------------------------------", font, new SolidBrush(Color.Black), startX, startY + offset);
offset = offset + (int)fontHeight + 5; //make the spacing consistent
while (results.Read())
{
try
{
newSalesPrint.sales_id = results.GetInt16(0);
newSalesPrint.drug_name = results.GetString(1);
newSalesPrint.quantity = results.GetString(2);
newSalesPrint.price = results.GetString(3);
newSalesPrint.pfno = results.GetString(4);
newSalesPrint.sales_date = results.GetString(5);
newSalesPrint.unit = results.GetString(6);
newSalesPrint.serial = results.GetString(7);
graphic.DrawString(newSalesPrint.drug_name.PadRight(12) + newSalesPrint.quantity.PadRight(5) +
newSalesPrint.price.PadRight(9) + newSalesPrint.pfno.PadRight(13) + newSalesPrint.unit.PadRight(7) + newSalesPrint.serial.PadRight(13) +
newSalesPrint.sales_date.PadRight(7), font, new SolidBrush(Color.Black), startX, startY + offset);
offset = offset + (int)fontHeight; //make the spacing consistent
graphic.DrawString("--------------------------------------------------------------------------------", font, new SolidBrush(Color.Black), startX, startY + offset);
offset = offset + (int)fontHeight + 5; //make the spacing consistent
}
catch (InvalidCastException er)
{
MessageBox.Show(er.Message);
}
}
con.Close();
offset = offset + 20; //make some room so that the footer stands out.
graphic.DrawString("Pharmacy Name".PadLeft(70), font, new SolidBrush(Color.Black), startX, startY + 1040);
offset = offset + 15;
graphic.DrawString("CopyRight @ 2015".PadLeft(72), font, new SolidBrush(Color.Black), startX, startY + 1040 + 20);
}
private void btn_set_mounters_Click(object sender, EventArgs e)
{
char a = Form1.Instance.index_Haut.Connection_User.Text[14];
this.pnl_week_orders_forMounter.Controls.Clear();
Orders = new Dictionary <Model_Bike, Client>();
MySqlDataReader reader = null;
String sql = "SELECT * from bikes, command,customer where bikes.id_command = command.id_command and customer.id_customer = command.id_customer order by bikes.production_order";
MySqlConnection connectionDB = Connection.connection();
connectionDB.Open();
try
{
MySqlCommand comando = new MySqlCommand(sql, connectionDB);
reader = comando.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Model_Bike bike = new Model_Bike(reader.GetString("colour"), reader.GetString("type_bike"), reader.GetString("size"), 1, 1);
bike.set_id(reader.GetInt16("id_bike"));
bike.set_order(reader.GetInt16("production_order"));
bike.set_status(reader.GetString("Status"));
bike.set_mounter(reader.GetInt16("Id_mounter").ToString());
Orders.Add(bike, new Client(reader.GetString("firstname"), reader.GetString("lastname"), reader.GetString("email"), reader.GetString("adress"), int.Parse(reader.GetString("postalcode")), reader.GetString("city"), reader.GetString("business_name")));
}
}
else
{
MessageBox.Show("NoOrderWasfound");
}
}
catch (Exception ex)
{
MessageBox.Show("Searching error" + ex.Message);
}
finally
{
connectionDB.Close();
}
foreach (KeyValuePair <Model_Bike, Client> elt in Orders)
{
MounterOrder order = new MounterOrder(elt.Key.id_bike.ToString(), elt.Key.type, elt.Key.colour, elt.Key.size, elt.Value.last_name, elt.Value.business_name, elt.Key.order.ToString(), elt.Key.status);
if (Form1.Instance.index_Haut.Connection_User.Text[14] == elt.Key.mounter[0] && order.Status_bike.Text != "Done")
{
order.Status_bike.Enabled = true;
}
else if (order.Status_bike.Text == "Waiting")
{
order.Status_bike.Enabled = true;
}
else
{
order.Status_bike.Enabled = false;
}
Form1.Instance.MounteurControl.pnl_week_orders_forMounter.Controls.Add(order);
}
}
public short GetInt16(int i)
{
return(reader.GetInt16(i));
}
//obtiene las variables del sitio operativo
public static void ExtractAndLoad(OperationalVariable variable)
{
DateTime initTime = DateTime.Now;
DateTime finishTime = DateTime.Now;
//conexion al central para saber el estado del storage y si es necesario crearlo
MySqlConnection connCentralEDR = new MySqlConnection(centralEDRconnStr);
try
{
Console.WriteLine("Extracting... " + variable.StorageName);
connCentralEDR.Open();
connCentralEDR.ChangeDatabase(DefaultCentralSchema);
string EXISTE_STORAGE = "SELECT COUNT(*) AS count " +
"FROM information_schema.tables " +
"WHERE table_schema = '{0}' " +
"AND table_name = '{1}'";
EXISTE_STORAGE = string.Format(EXISTE_STORAGE, DefaultCentralSchema, variable.StorageName);
string CREATE_STORAGE = "CREATE TABLE {0}( " +
"id_site int(11) DEFAULT NULL, " +
"id_variable int(11) DEFAULT NULL," +
"value smallint(4) DEFAULT NULL," +
"ts timestamp(6) ," +
"mts smallint(6) DEFAULT NULL" +
")" +
"ENGINE = INNODB," +
"CHARACTER SET utf8," +
"COLLATE utf8_spanish_ci," +
"COMMENT = 'Tabla para la variable {0}'" +
"PARTITION BY KEY(id_site)" +
"(" +
"PARTITION partitionby_site ENGINE = INNODB" +
");" +
"ALTER TABLE {0} " +
"ADD INDEX UK_{0}(id_site);";
CREATE_STORAGE = string.Format(CREATE_STORAGE, variable.StorageName);
//configuramos la sentencia de Extraccion de datos del Site Remoto
string EXTRACT_QUERY = "SELECT DISTINCT value, ts, mts FROM {0}.{1} WHERE ts > '{2}' ORDER BY ts ASC LIMIT 20000";
EXTRACT_QUERY = string.Format(EXTRACT_QUERY
, SiteConf.SiteSchema
, variable.StorageName
, variable.StringLastSync);
MySqlCommand cmd = new MySqlCommand(EXISTE_STORAGE, connCentralEDR);
MySqlDataReader rdr = cmd.ExecuteReader();
bool existeStorage = false;
if (rdr.Read())
{
existeStorage = rdr.GetInt16("count") == 1;
}
Console.WriteLine("Existe Storage? " + existeStorage.ToString());
rdr.Close();
if (!existeStorage)
{
cmd.CommandText = string.Format(CREATE_STORAGE, variable.StorageName);
Console.WriteLine("CREATE_STORAGE... " + CREATE_STORAGE);
cmd.ExecuteNonQuery();
Console.WriteLine("CREATE_STORAGE... Ok");
}
//ahora nos conectamos al sitio remoto para extraer los datos
MySqlConnection connOnSite = new MySqlConnection(onSiteEDRconnStr);
DataTable tblOrigen = new DataTable();
DateTime? LastTimeStamp = variable.LastSync;
string remark = string.Empty;
try
{
connOnSite.Open();
tblOrigen = GetDataTableLayout(variable.StorageName, connOnSite);
tblOrigen.Columns.Add("id_site");
//tblOrigen.NewRow();
Console.WriteLine("EXTRACT_QUERY...");
MySqlCommand cmdExtract = new MySqlCommand(EXTRACT_QUERY, connOnSite);
MySqlDataReader etlReader = cmdExtract.ExecuteReader();
Console.WriteLine("EXTRACT_QUERY...Ok");
while (etlReader.Read())
{
var r = tblOrigen.NewRow();
r["id_site"] = SiteConf.IdSite;
r["id_variable"] = 0;
r["value"] = etlReader.GetFloat("value");
r["ts"] = etlReader.GetDateTime("ts");
r["mts"] = etlReader.GetFloat("mts");
tblOrigen.Rows.Add(r);
//Console.WriteLine(r["value"].ToString() + " | " + r["ts"].ToString());
r = null;
rowExtractCount++;
LastTimeStamp = etlReader.GetDateTime("ts"); //guardamos el valor del time stamp para obtener el ultimo para la siguiente actualización incremental
}
if (rowExtractCount == 0)
{
remark = "-No hay datos desde la última sincronización";
}
}
catch (Exception innerEx)
{
Console.WriteLine(innerEx.ToString());
}
finally
{
if (connOnSite.State == ConnectionState.Open)
{
connOnSite.Close();
}
}
Console.WriteLine("Loading... " + variable.StorageName);
currentVariable = variable.StorageName;
rowInsertedCount = BulkInsertMySQL(tblOrigen, variable.StorageName, connCentralEDR);
remark = "Ok" + remark;
//update delta
string UPDATE_DELTA = "UPDATE delta " +
"SET row_count_source = {0} " +
", row_count_destiny = '{1}' " +
", row_last_timestamp = '{2}' " +
", finish_date = NOW() " +
", remark = '{3}' " +
"WHERE id_delta = {4}";
UPDATE_DELTA = string.Format(UPDATE_DELTA
, rowExtractCount //número de filas recuperadas
, rowInsertedCount //nùmero de filas insertadas
, ETLHelper.ConvertDateToYYYMMDD(LastTimeStamp)
, remark
, variable.DeltaID
);
MySqlCommand cmdUpdateDelta = new MySqlCommand(UPDATE_DELTA, connCentralEDR);
cmdUpdateDelta.ExecuteNonQuery();
connCentralEDR.Close();
finishTime = DateTime.Now;
long elapsedTime = finishTime.Ticks - initTime.Ticks;
TimeSpan elapsedSpan = new TimeSpan(elapsedTime);
Console.WriteLine("ETL " + variable.StorageName + " - Start: " + initTime.ToString() + " Finish:" + finishTime.ToString() + " Elapsed time: " + elapsedSpan.TotalSeconds.ToString());
//Console.WriteLine("Row count: " + rowCount.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if (connCentralEDR.State == ConnectionState.Open)
{
connCentralEDR.Close();
}
}
}