private void FarmerPayment_Load(object sender, EventArgs e)
{
Temp1.Open(@"select * from farmerpayment", Program.DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
if (Temp1.RecordCount == 0)
{
textBox1.Text = "1";
}
else
{
textBox1.Text = (Temp1.RecordCount + 1).ToString();
}
Temp1.Close();
textBox1.Enabled = false;
textBox2.Enabled = false;
Temp2.Open(@"select * from farmerdetails", Program.DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
while (Temp2.EOF == false)
{
Column1.Items.Add(Temp2.Fields["fNames"].Value.ToString());
Temp2.MoveNext();
}
Temp2.Close();
// comboBox2.Properties.DropDownStyle = ComboBoxStyle.DropDownList;
comboBox2.Properties.Items.Add("CASH");
comboBox2.Properties.Items.Add("CHEQUE");
label7.Visible = false;
textBox5.Visible = false;
dataGridView1.EditingControlShowing += new DataGridViewEditingControlShowingEventHandler(dataGrid1_Edit);
}
private void coletadados()
{
ADODB.Recordset dados = new ADODB.Recordset();
ADODB.Recordset itens = new ADODB.Recordset();
String SQL = "SELECT * FROM SAÍDA WHERE (COD_SAI = " + _id + ");";
String SQLProdutos = "SELECT COD, ESTOQUE FORM PRODUTOS;";
//Buscando os itens
dados.Open(SQL, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
itens.Open(SQLProdutos, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
while (!(dados.EOF || dados.BOF))
{
itens.Find("cod =" + dados.Fields["[cod do CD]"].Value, 0, ADODB.SearchDirectionEnum.adSearchForward);
if (!(itens.BOF || itens.EOF))
{
itens.Fields["Estoque"].Value = Convert.ToInt64(itens.Fields["Estoque"].Value) + Convert.ToInt64(dados.Fields["Qunatidade"].Value);
}
dados.MoveNext();
}
//EXcluindo ITENS
SQL = "DELETE FROM SAÍDA WHERE (COD_SAI = " + _id + ");";
dados.Close();
dados.Open(SQL, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
//Excluindo capa
SQL = "DELETE FROM cod_sai WHERE(COD_SAI = " + _id + ");";
dados.Close();
dados.Open(SQL, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
}
private void custmreceipt_Load(object sender, EventArgs e)
{
textBox1.Enabled = false;
textBox2.Enabled = false;
textBox4.Text = "0";
textBox3.Text = "0";
// comboBox2.DropDownStyle = ComboBoxStyle.DropDownList;
comboBox2.Properties.Items.Add("CASH");
comboBox2.Properties.Items.Add("CHEQUE");
// comboBox1.Properties.DropDownStyle = ComboBoxStyle.DropDownList;
label7.Visible = false;
textBox5.Visible = false;
Temp1.Open(@"select * from customerdetails", Program.DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
while (Temp1.EOF == false)
{
comboBox1.Properties.Items.Add(Temp1.Fields["CName"].Value);
Temp1.MoveNext();
}
Temp1.Close();
Temp1.Open(@"select * from payments", Program.DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
if (Temp1.RecordCount == 0)
{
textBox1.Text = "1";
}
else
{
Temp1.MoveLast();
int a = Temp1.Fields["payID"].Value + 1;
textBox1.Text = a.ToString();
Temp1.MoveNext();
}
Temp1.Close();
}
//Lagerbestand
private void metroComboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
try {
cn.Open(cnStr);
string periode = metroComboBox1.Text;
if (periode == "0")
{
rs.Open("Select Startmenge, Artikelnummer, Startpreis From Artikelstammdaten", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
int i = 0;
metroGrid5.Rows.Clear();
while (!rs.EOF)
{
metroGrid5.Rows.Add();
metroGrid5.Rows[i].Cells["period"].Value = Convert.ToInt32(periode);
metroGrid5.Rows[i].Cells["id"].Value = Convert.ToInt32(rs.Fields["Artikelnummer"].Value);
metroGrid5.Rows[i].Cells["actualStock"].Value = Convert.ToInt32(rs.Fields["Startmenge"].Value);
metroGrid5.Rows[i].Cells["actualPrice"].Value = Convert.ToDouble(rs.Fields["Startpreis"].Value);
//metroGrid5.Rows[i].Cells["stockValue"].Value = ;
rs.MoveNext();
i++;
}
}
else
{
rs.Open("Select * From warehousestock_article where period ='" + periode + "'", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
int i = 0;
metroGrid5.Rows.Clear();
while (!rs.EOF)
{
metroGrid5.Rows.Add();
metroGrid5.Rows[i].Cells["period"].Value = Convert.ToInt32(rs.Fields["period"].Value);
metroGrid5.Rows[i].Cells["id"].Value = Convert.ToInt32(rs.Fields["id"].Value);
metroGrid5.Rows[i].Cells["actualStock"].Value = Convert.ToInt32(rs.Fields["amount"].Value);
metroGrid5.Rows[i].Cells["actualPrice"].Value = Convert.ToDouble(rs.Fields["price"].Value);
metroGrid5.Rows[i].Cells["stockValue"].Value = Convert.ToDouble(rs.Fields["stockValue"].Value);
rs.MoveNext();
i++;
}
}
rs.Close();
}
catch (Exception fehler)
{
Console.WriteLine("Ein Fehler ist aufgetreten", fehler); //Try catch überprüfen....
}
finally
{
cn.Close();
}
}
private void metroComboBox4_SelectedIndexChanged(object sender, EventArgs e)
{
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
try
{
cn.Open(cnStr);
string periode = metroComboBox4.Text;
if (periode == "0")
{
rs.Open("Select * From summary", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
metroGrid4.Rows.Clear();
int i = 0;
while (!rs.EOF)
{
metroGrid1.Rows.Add();
metroGrid1.Rows[i].Cells["Gewinn"].Value = rs.Fields["profit_current"].Value;
metroGrid1.Rows[i].Cells["GewinnDurchschnitt"].Value = rs.Fields["profit_average"].Value;
metroGrid1.Rows[i].Cells["GewinnGesamt"].Value = rs.Fields["profit_all"].Value;
rs.MoveNext();
++i;
}
//i = 0;
}
else
{
rs.Open("Select * From summary", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
metroGrid4.Rows.Clear();
int i = 0;
while (!rs.EOF)
{
metroGrid1.Rows.Add();
metroGrid1.Rows[i].Cells["Gewinn"].Value = rs.Fields["profit_current"].Value;
metroGrid1.Rows[i].Cells["GewinnDurchschnitt"].Value = rs.Fields["profit_average"].Value;
metroGrid1.Rows[i].Cells["GewinnGesamt"].Value = rs.Fields["profit_all"].Value;
rs.MoveNext();
++i;
}
//i = 0;
}
rs.Close();
}
catch (Exception fehler)
{
Console.WriteLine("Ein Fehler ist aufgetreten", fehler); //Try catch überprüfen....
}
finally
{
cn.Close();
}
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'clientDBDataSet.ClientTable' table. You can move, or remove it, as needed.
//this.clientTableTableAdapter.Fill(this.clientDBDataSet.ClientTable);
Con = new ADODB.Connection();
Rs = new ADODB.Recordset();
//Connection calling partner object Provider, Path file, access string
Con.Provider = Obj.MSAccessObj.Provider;
Con.ConnectionString = Obj.MSAccessObj.PathFile;
Con.Open();
//partnerObj.CAccess.AccessString = "select * from " in Class
String accessStr;
accessStr = Obj.MSAccessObj.AccessString + accessTableName;
Rs.Open(accessStr, Con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
// connection in sql DB
cn = new System.Data.OleDb.OleDbConnection();
// A dataset for sql table
ds = new DataSet();
//sqlCon.ConnectionString = Obj.MSSQLObj.DataSource + Obj.MSSQLObj.AttachedDB + Obj.MSSQLObj.IntSecurity;
cn.ConnectionString = "Provider = SQLNCLI11.0;Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\ITD\\Term 3\\C#\\assignment\\assignment7\\AwareObject\\AwareObject\\AwareSQLDB.mdf;Integrated Security = SSPI;database=thingy";
cn.Open();
// Attempting to access Admin Table
String sqlstr;
sqlstr = Obj.MSSQLObj.SqlStr + sqlTableName;
da = new System.Data.OleDb.OleDbDataAdapter(sqlstr, cn);
da.Fill(ds, sqlTableName);
RecordCount = ds.Tables[sqlTableName].Rows.Count;
}
//传入SQL语句，得到该SQL语句执行的结果并返回。
private object[,] plSqlReturn(string sQuery)
{
object[,] ArrayRecords = null;
RsRowCount = 0;
RsColCount = 0;
ADODB.Recordset AdoRs;
try
{
if (GeneralCommon.M_CN1.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return(null);
}
}
AdoRs = new ADODB.Recordset();
AdoRs.Open(sQuery, GeneralCommon.M_CN1, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly, -1);
if (AdoRs.BOF || AdoRs.EOF)
{
GeneralCommon.Gp_MsgBoxDisplay("没有数据...!", "I", "提示");
AdoRs.Close();
AdoRs = null;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
Cursor.Current = Cursors.Default;
return(null);
}
RsRowCount = AdoRs.RecordCount;
RsColCount = AdoRs.Fields.Count;
ArrayRecords = new object[RsRowCount, RsColCount];
int i = 0;
while (!AdoRs.EOF)
{
for (int j = 0; j < AdoRs.Fields.Count; j++)
{
ArrayRecords[i, j] = AdoRs.Fields[j].Value;
}
i++;
AdoRs.MoveNext();
}
AdoRs.Close();
AdoRs = null;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
return(ArrayRecords);
}
catch (Exception ex)
{
AdoRs = null;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
return(null);
}
}
/****** View with Source Object ******/
private ADODB.Recordset ConvertToRecordset(DataTable inTable)
{
ADODB.Recordset result = new ADODB.Recordset();
result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
ADODB.Fields resultFields = result.Fields;
System.Data.DataColumnCollection inColumns = inTable.Columns;
foreach (DataColumn inColumn in inColumns)
{
resultFields.Append(inColumn.ColumnName
, TranslateType(inColumn.DataType)
, inColumn.MaxLength
, inColumn.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable : ADODB.FieldAttributeEnum.adFldUnspecified
, null);
}
result.Open(System.Reflection.Missing.Value
, System.Reflection.Missing.Value
, ADODB.CursorTypeEnum.adOpenStatic
, ADODB.LockTypeEnum.adLockOptimistic, 0);
foreach (DataRow dr in inTable.Rows)
{
result.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
for (int columnIndex = 0; columnIndex < inColumns.Count; columnIndex++)
{
resultFields[columnIndex].Value = dr[columnIndex];
}
}
return(result);
}
private void Form1_Load(object sender, EventArgs e)
{
String Sqlstr1, Sqlstr2;
Cn = new ADODB.Connection();
Rscar = new ADODB.Recordset();
Rsuser = new ADODB.Recordset();
Cn.Provider = "Microsoft.Jet.Oledb.4.0";
Cn.ConnectionString = "DataBase\\Cars.mdb";
Cn.Open();
Sqlstr1 = "select * from Car";
Sqlstr2 = "select * from Customer";
Rscar.Open(Sqlstr1, Cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
Rsuser.Open(Sqlstr2, Cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
menu.Parent = tab;
info.Parent = null;
customer.Parent = null;
modify.Parent = null;
returnCar.Parent = null;
Rscar.MoveFirst();
//Rsuser.MoveFirst();
}
/// <summary>
/// Reads a string that is stored in a Object.
/// </summary>
/// <param name="variableName">The name under which the string is stored.</param>
/// <returns>The value converted to string.</returns>
public string ReadLongStringVariable(string variableName)
{
if (Dts.Variables[variableName].DataType.Equals(typeof(string)))
{
return(Dts.Variables[variableName].Value.ToString());
}
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();
ADODB.Stream stm = new ADODB.Stream();
ADODB.Recordset rs = ((ADODB.Recordset)Dts.Variables[variableName].Value).Clone();
ADODB.Recordset rsCopy = new ADODB.Recordset();
rs.Save(stm);
rsCopy.Open(stm);
da.Fill(dt, rs);
dt.Clear();
da.Fill(dt, rsCopy);
if (
dt.Rows.Count > 0 &&
dt.Rows[0].ItemArray.Count() > 0
)
{
return(dt.Rows[0].ItemArray[0].ToString());
}
return(null);
}
public ADODB.Recordset fnExecuteSQL(String SQL)
{
FrmConfig FrmConfig = new FrmConfig();
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
FrmConfig.carregaConfig();
String Banco = FrmConfig.Banco;
String Servidor = FrmConfig.Servidor;
String Usuario = FrmConfig.Usuario;
String Senha = FrmConfig.Senha;
//String Encript = true;
String cnStr = "Provider=SQLOLEDB;Initial Catalog=" + Banco
+ ";Data Source=" + Servidor
+ ";User ID=" + Usuario
+ ";Password=" + Senha;
rs.Open(SQL, cnStr, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
//rs.Close();
return(rs);
}
public static string ReadExcel(string FilePath, int C, int R)
{
ADODB.Connection conn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
string Sql = "Select * From [Sheet1$]";
//Select 宗地编码,土地类型,承包方名称,承包方编码 From [地块信息$] Order By 承包方编码,宗地编码
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + FilePath);
rs.Open(Sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly, -1);
object ary = rs.GetRows();
object[,] aa = (object[, ])ary;
rs.Close();
conn.Close();
if ((string)aa[C - 1, R - 2] == null)
{
return("");
}
return((string)aa[C - 1, R - 2]);
}
//METODO QUE VERIFICQUE SI EXISTE UNA INFORMACION EN LA TABLA
// DE LA BASE DE DATOS
public bool MYSQL_EXISTE_DATO(string instruccionSQL)
{
this.establecerConexionODBC();
bool functionReturnValue = false;
ADODB.Recordset rst = new ADODB.Recordset();
rst.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
try
{
rst.Open(instruccionSQL, MYSQL_CONEXION_ODBC, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, -1);
if (!rst.EOF)
{
functionReturnValue = true;
}
else
{
functionReturnValue = false;
}
rst.Close();
}
catch (System.Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
}
rst = null;
this.cerrarConexionODBC();
return(functionReturnValue);
}
public static ADODB.Recordset getRSreport1(ref object sql)
{
ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
// ERROR: Not supported in C#: OnErrorStatement
functionReturnValue = new ADODB.Recordset();
functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
functionReturnValue.Open(sql, cnnDBConsReport, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
return(functionReturnValue);
getRSreport1_Error:
if (cnnDBConsReport == null)
{
Interaction.MsgBox(Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " cnnDBConsReport object has not been made.");
}
else
{
Interaction.MsgBox("Error while getRSreport1 and Error is :" + Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " --- " + cnnDBConsReport.ConnectionString + Constants.vbCrLf + Constants.vbCrLf + " --- " + cnnDBConsReport.State);
}
// ERROR: Not supported in C#: ResumeStatement
return(functionReturnValue);
}
public void CallingCLRMethodsThatHaveValueTypeParametersWorksWithReferenceTypes()
{
var recordset = new ADODB.Recordset();
recordset.Fields.Append("name", ADODB.DataTypeEnum.adVarChar, 20, ADODB.FieldAttributeEnum.adFldUpdatable);
recordset.Open(CursorType: ADODB.CursorTypeEnum.adOpenUnspecified, LockType: ADODB.LockTypeEnum.adLockUnspecified, Options: 0);
recordset.AddNew();
recordset.Fields["name"].Value = "TestName";
recordset.Update();
var _ = DefaultRuntimeSupportClassFactory.DefaultVBScriptValueRetriever;
object objField = _.CALL(
context: null,
target: recordset,
members: new string[0],
argumentProviderBuilder: _.ARGS.Val("name")
);
Assert.Equal(
"TestName",
_.CALL(
context: null,
target: this,
member1: "MockMethodReturningInputString",
argumentProviderBuilder: _.ARGS.Ref(objField, v => { objField = v; })
)
);
}
private void textBox1_KeyUp(object sender, KeyEventArgs e)
{
if (this.textBox1.Text == "")
{
this.comboBox1.Items.Clear();
this.comboBox1.Text = "";
}
else
{
this.comboBox1.Text = "";
string sql = GetQuery();
ADODB.Recordset AdoRs = new ADODB.Recordset();
if (GeneralCommon.M_CN1.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return;
}
}
AdoRs.Open(sql, GeneralCommon.M_CN1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, -1);
this.comboBox1.Items.Clear();
while (!AdoRs.EOF)
{
this.comboBox1.Items.Add(AdoRs.Fields[0].Value);
AdoRs.MoveNext();
}
}
}
public void TestDbRunSql()
{
ADODB.Connection conn = null;
AdoHelper helper = null;
try
{
using (helper = AdoHelper.ThreadInstance("", DbProviderType.Odbc))
{
conn = helper.Connection();
Assert.AreEqual(conn.State, 1);
string sql = " ";
ADODB.Recordset rs = new ADODB.Recordset();
rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenUnspecified, ADODB.LockTypeEnum.adLockUnspecified, -1);
int a = int.Parse(rs.Fields[0].Value.ToString());
rs.Close();
Assert.IsTrue((a > 0));
}
}
catch (Exception ex)
{
Console.WriteLine(string.Format("{0}\n{1}", ex.Message, ex.ToString()));
throw;
}
Assert.AreEqual(conn.State, 0);
}
static List <MyParagraph> getTitleParagraphs(ADODB.Connection cnn, UInt64 titleId)
{
var paragraphLst = new List <MyParagraph>();
//get title
var qry = "select * from paragraphs where titleId = ? ";
var rst = new ADODB.Recordset();
rst.Open(qry.Replace("?", titleId.ToString()),
cnn, ADODB.CursorTypeEnum.adOpenKeyset,
ADODB.LockTypeEnum.adLockOptimistic,
(int)ADODB.CommandTypeEnum.adCmdText);
rst.MoveFirst();
while (!rst.EOF)
{
var par = new MyParagraph();
par.order = Convert.ToInt32(rst.Fields["order"].Value);
par.alignment = Convert.ToInt32(rst.Fields["alignment"].Value);
par.leftIndent = Convert.ToInt32(rst.Fields["leftIndent"].Value);
par.fontSize = Convert.ToInt32(rst.Fields["fontSize"].Value);
par.fontBold = Convert.ToInt32(rst.Fields["fontBold"].Value);
par.fontItalic = Convert.ToInt32(rst.Fields["fontItalic"].Value);
par.content = Convert.ToString(rst.Fields["content"].Value);
paragraphLst.Add(par);
rst.MoveNext();
}
rst.Close();
return(paragraphLst);
}
private void menge_Produkt3_Button_Click(object sender, EventArgs e)
{
metroGrid3.Rows.Clear();
//Recordset
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
try
{
cn.Open(cnStr);
rs.Open("Select Artikel, Sum(Anzahl) as Anzahl_Menge From Listen where Endprodukt = 3 group by Artikel", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
int i = 0;
while (!rs.EOF)
{
metroGrid3.Rows.Add();
metroGrid3.Rows[i].Cells["Artikel_Mengen"].Value = Convert.ToInt32(rs.Fields["Artikel"].Value);
metroGrid3.Rows[i].Cells["Anzahl_Menge"].Value = Convert.ToInt32(rs.Fields["Anzahl_Menge"].Value);
rs.MoveNext();
i++;
}
rs.Close();
}
catch (Exception fehler)
{
Console.WriteLine("Ein Fehler ist aufgetreten", fehler); //Try catch überprüfen....
}
finally
{
cn.Close();
}
}
private void ErgebnisseForm_LoadBestellungen(ADODB.Connection cn, ADODB.Recordset rs)
{
rs.Open("Select * From myOrderlist", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
int i = 0;
while (!rs.EOF)
{
metroGrid1.Rows.Add();
metroGrid1.Rows[i].Cells["Artikelnummer_Bestellung"].Value = rs.Fields["article"].Value;
metroGrid1.Rows[i].Cells["Menge_Bestellung"].Value = rs.Fields["quantity"].Value;
// Überprüfung auf Eil oder Normalbestellung
if (Convert.ToInt32(rs.Fields["modus"].Value) == 4)
{
metroGrid1.Rows[i].Cells["Bestellart_Bestellung"].Value = "Eil";
}
else if (Convert.ToInt32(rs.Fields["modus"].Value) == 5)
{
metroGrid1.Rows[i].Cells["Bestellart_Bestellung"].Value = "Normal";
}
rs.MoveNext();
++i;
}
i = 0;
rs.Close();
}
private void ErgebnisseForm_LoadZugang(ADODB.Connection cn, ADODB.Recordset rs)
{
rs.Open("Select * From Lagerzugang", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
int i = 0;
while (!rs.EOF)
{
metroGrid1.Rows.Add();
metroGrid1.Rows[i].Cells["Artikelnummer_Zugang"].Value = rs.Fields["article"].Value;
metroGrid1.Rows[i].Cells["Bestellperiode_Zugang"].Value = rs.Fields["orderperiod"].Value;
// Überprüfung auf Eil oder Normalbestellung
if (Convert.ToInt32(rs.Fields["modus"].Value) == 4)
{
metroGrid1.Rows[i].Cells["Modus_Zugang"].Value = "Eil";
}
else if (Convert.ToInt32(rs.Fields["modus"].Value) == 5)
{
metroGrid1.Rows[i].Cells["Modus_Zugang"].Value = "Normal";
}
metroGrid1.Rows[i].Cells["Lieferzeit_Zugang"].Value = rs.Fields["Lieferzeit"].Value;
metroGrid1.Rows[i].Cells["Menge_Zugang"].Value = rs.Fields["amount"].Value;
metroGrid1.Rows[i].Cells["Preis_Zugang"].Value = rs.Fields["price"].Value;
rs.MoveNext();
++i;
}
i = 0;
rs.Close();
}
private ADODB.Recordset CreateInMemoryADORS(IGTKeyObjects DDCKeyObjects)
{
ADODB.Recordset shapeADORecordset = new ADODB.Recordset();
try
{
shapeADORecordset.Fields.Append("G3E_FNO", ADODB.DataTypeEnum.adInteger, 0, ADODB.FieldAttributeEnum.adFldFixed, null);
shapeADORecordset.Fields.Append("G3E_FID", ADODB.DataTypeEnum.adInteger, 0, ADODB.FieldAttributeEnum.adFldFixed, null);
shapeADORecordset.Open(System.Type.Missing, System.Type.Missing, ADODB.CursorTypeEnum.adOpenUnspecified, ADODB.LockTypeEnum.adLockUnspecified, 0);
foreach (IGTKeyObject keyObject in DDCKeyObjects)
{
shapeADORecordset.AddNew(System.Type.Missing, System.Type.Missing);
shapeADORecordset.Fields["G3E_FNO"].Value = keyObject.FNO;
shapeADORecordset.Fields["G3E_FID"].Value = keyObject.FID;
shapeADORecordset.Update(System.Type.Missing, System.Type.Missing);
}
}
catch (Exception ex)
{
string exMsg = string.Format("Error occurred in {0} of {1}.{2}{3}", System.Reflection.MethodBase.GetCurrentMethod().Name, this.ToString(), Environment.NewLine, ex.Message);
throw new Exception(exMsg);
}
return(shapeADORecordset);
}
private void StammdatenForm_Load(object sender, EventArgs e)
{
//Recordset
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
//Connection string.
cn.Open(cnStr);
rs.Open("Select * From Artikelstammdaten", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
int i = 0;
while (!rs.EOF)
{
metroGrid1.Rows.Add();
metroGrid1.Rows[i].Cells["Artikelnummer"].Value = Convert.ToInt32(rs.Fields["Artikelnummer"].Value);
metroGrid1.Rows[i].Cells["Bezeichnung"].Value = rs.Fields["Bezeichnung"].Value;
metroGrid1.Rows[i].Cells["Verwendung"].Value = rs.Fields["Verwendung"].Value;
metroGrid1.Rows[i].Cells["Lieferkosten"].Value = rs.Fields["Lieferkosten"].Value;
metroGrid1.Rows[i].Cells["Lieferzeit"].Value = rs.Fields["Lieferzeit"].Value;
metroGrid1.Rows[i].Cells["Lieferzeitabweichung"].Value = rs.Fields["Lieferzeitabweichung"].Value;
rs.MoveNext();
++i;
}
i = 0;
rs.Close();
rs.Open("Select * From PersMaschKosten", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
while (!rs.EOF)
{
metroGrid4.Rows.Add();
metroGrid4.Rows[i].Cells["Arbeitsplatz"].Value = Convert.ToInt32(rs.Fields["Arbeitsplatz"].Value);
metroGrid4.Rows[i].Cells["Lohn1"].Value = Convert.ToDouble(rs.Fields["1Lohn"].Value);
metroGrid4.Rows[i].Cells["Lohn2"].Value = Convert.ToDouble(rs.Fields["2Lohn"].Value);
metroGrid4.Rows[i].Cells["Lohn3"].Value = Convert.ToDouble(rs.Fields["3Lohn"].Value);
metroGrid4.Rows[i].Cells["ÜLohn"].Value = Convert.ToDouble(rs.Fields["ÜLohn"].Value);
metroGrid4.Rows[i].Cells["varKosten"].Value = Convert.ToDouble(rs.Fields["varKosten"].Value);
metroGrid4.Rows[i].Cells["fixeKosten"].Value = Convert.ToDouble(rs.Fields["fixeKosten"].Value);
rs.MoveNext();
++i;
}
rs.Close();
cn.Close();
refreshComboBox1();
}
public ADODB.Recordset getRSwaitron(ref object sql)
{
ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
functionReturnValue = new ADODB.Recordset();
functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
functionReturnValue.Open(sql, cnnDBWaitron, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
return(functionReturnValue);
}
public static ADODB.Recordset getRSwaitron(ref object sql, ref ADODB.Connection cn)
{
ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
functionReturnValue = new ADODB.Recordset();
functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
functionReturnValue.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
return functionReturnValue;
}
//==============================================================================================
// Procedure Name : Cmd_OK_ClickEvent
// Description :
//==============================================================================================
private void Cmd_OK_ClickEvent(object sender, System.EventArgs e)
{
Login Login = new Login();
string sQuery;
ADODB.Recordset AdoRs;
AdoRs = new ADODB.Recordset();
try
{
if (txt_newpassword.Text.Trim() == "")
{
GeneralCommon.Gp_MsgBoxDisplay("请输入新密码..!!", "I", "");
return;
}
if (txt_confirm.Text.Trim() == "")
{
GeneralCommon.Gp_MsgBoxDisplay("请再次输入新密码..!!", "I", "");
return;
}
if (txt_newpassword.Text.Trim() == txt_confirm.Text.Trim())
{
Login.txt_password.Text = "";
//Db Connection Check
if (GeneralCommon.GF_DbConnect() == false)
{
return;
}
sQuery = "Update zp_EMPLOYEE set PASSWORD = gf_md5_pwd('" + txt_confirm.Text.Trim() + "') ";
sQuery = sQuery + " where EMP_ID = '" + GeneralCommon.sUserID + "' ";
//Ado Execute
AdoRs.Open(sQuery, GeneralCommon.M_CN1, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly, -1);
//AdoRs.Close()
AdoRs = null;
this.Close();
}
else
{
GeneralCommon.Gp_MsgBoxDisplay("输入的密码不一致..!!", "I", "");
}
AdoRs = null;
}
catch (Exception ex)
{
AdoRs = null;
GeneralCommon.Gp_MsgBoxDisplay((string)("修改密码失败..!!" + ex.Message), "", "");
}
}
private bool Addr_Display()
{
bool isHaveData = false;
if (GeneralCommon.M_CN1.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return(isHaveData);
}
}
ADODB.Recordset AdoRs = new ADODB.Recordset();
try
{
string sQuery1 = " SELECT LOCATION FROM FP_STDYARD WHERE PROD_TYPE = 'S' AND YARD_TYPE = '" + rib_prev + "' AND ZONE_TYPE = '" + opt_prev + "' AND YARD_KND = 'ZB' ";
AdoRs.Open(sQuery1, GeneralCommon.M_CN1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, -1);
if (AdoRs.EOF || AdoRs.BOF)
{
AdoRs.Close();
GeneralCommon.M_CN1.Close();
this.ss1_Sheet1.RowCount = 0;
this.ss1_Sheet1.ColumnCount = 0;
return(isHaveData);
}
while (!AdoRs.EOF)
{
string temp = AdoRs.Fields[0].Value.ToString();
int rowIndex = int.Parse(temp.Substring(3, 2));
int columnIndex = int.Parse(temp.Substring(5, 2));
this.ss1_Sheet1.Cells[rowIndex * 2 - 2, columnIndex - 1].Text = temp;
this.ss1_Sheet1.Cells[rowIndex * 2 - 2, columnIndex - 1].Tag = temp;
this.ss1_Sheet1.Cells[rowIndex * 2 - 1, columnIndex - 1].Tag = temp;
AdoRs.MoveNext();
}
isHaveData = true;
AdoRs.Close();
AdoRs = null;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
return(isHaveData);
}
catch (Exception ex)
{
AdoRs.Close();
AdoRs = null;
GeneralCommon.Gp_MsgBoxDisplay("数据错误，请检查是否有异常数据...!!" + ex.Message, "W", "警告");
Cursor.Current = Cursors.Default;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
return(isHaveData);
}
}
public static ADODB.Recordset getRS(ref string sql)
{
ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
functionReturnValue = new ADODB.Recordset();
functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
functionReturnValue.Open(sql, cnnDB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
return(functionReturnValue);
//Debug.Print(sql)
}
public static ADODB.Recordset getRS(ref string sql)
{
ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
functionReturnValue = new ADODB.Recordset();
functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
functionReturnValue.Open(sql, cnnDB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
return functionReturnValue;
//Debug.Print(sql)
}
public bool ajustaprecos(double valor, int grupo)
{
String SQL = "UPDATE produtos SET Expr5 = Expr5 * " + valor + ", Expr7 = Expr7 * " + valor + ", Expr6 = Expr6 *" + valor + " WHERE (((produtos.tipo)=" + grupo + "));";
ADODB.Recordset dados = new ADODB.Recordset();
dados.Open(SQL, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
return(true);
}
public ViewReport(string titleReport, string rptFile, string querySql, string[] paramName, object[] paramValues, bool staticPrinter)
{
InitializeComponent();
this.Text = titleReport;
CRAXDDRT.Application crxApp = new CRAXDDRT.Application();
CRAXDDRT.Report report = new CRAXDDRT.Report();
ADODB.Connection conn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
string connstr = "Provider=SQLOLEDB;Data Source=" + SqlHelper.IPAddress + ";Initial Catalog=" + SqlHelper.DatabaseName + ";User Id=" + SqlHelper.UserID + ";Password="******";";
string reportFile = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory())) + "/Report/" + rptFile;
try
{
conn.Open(connstr, null, null, 0);
report = crxApp.OpenReport(reportFile, OpenReportMethod.OpenReportByDefault);
report.DiscardSavedData();
if (paramName != null)
{
for (int i = 0; i < paramName.Length; i++)
{
report.ParameterFields.GetItemByName(paramName[i], null).AddCurrentValue(paramValues[i]);
}
}
rs.Open(
querySql,
// SQL statement / table,view name /
// stored procedure call / file name
conn, // Connection / connection string
ADODB.CursorTypeEnum.adOpenForwardOnly, // Cursor type. (forward-only cursor)
ADODB.LockTypeEnum.adLockOptimistic, // Lock type. (locking records only
// when you call the Update method.
(int)ADODB.CommandTypeEnum.adCmdText);
/* For Execute StoreProcedure */
// rs.Open(querySql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
report.Database.SetDataSource(rs, 3, 1);
if (staticPrinter)
{
string printerName = Configurations.getPrinter();
report.SelectPrinter("", printerName, "");
//report.PrintOut(false, 1, true, 1, 200);
}
this.axCrystalActiveXReportViewer1.ReportSource = report;
this.axCrystalActiveXReportViewer1.ViewReport();
}
catch (Exception ex)
{
//this.Dispose();
//this.Close();
MessageBox.Show(ex.Message);
}
}
public ViewReport
(
string titleReport,
string rptFile,
string procedureName,
string procedureParam,
string[] rptParamName,
object[] rptParamValues,
bool staticPrinter
)
{
InitializeComponent();
this.Text = titleReport;
CRAXDDRT.Application crxApp = new CRAXDDRT.Application();
CRAXDDRT.Report report = new CRAXDDRT.Report();
ADODB.Connection conn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
string connstr = "Provider=SQLOLEDB;Data Source=" + SqlHelper.IPAddress + ";Initial Catalog=" + SqlHelper.DatabaseName + ";User Id=" + SqlHelper.UserID + ";Password="******";";
string reportFile = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory())) + "/Report/" + rptFile;
try
{
conn.Open(connstr, null, null, 0);
report = crxApp.OpenReport(reportFile, OpenReportMethod.OpenReportByDefault);
report.DiscardSavedData();
if (rptParamName != null)
{
for (int i = 0; i < rptParamName.Length; i++)
{
report.ParameterFields.GetItemByName(rptParamName[i], null).AddCurrentValue(rptParamValues[i]);
}
}
rs.Open(procedureName, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
report.Database.SetDataSource(rs, 3, 1);
if (staticPrinter)
{
string printerName = Configurations.getPrinter();
report.SelectPrinter("", printerName, "");
//report.PrintOut(false, 1, true, 1, 200);
}
this.axCrystalActiveXReportViewer1.ReportSource = report;
this.axCrystalActiveXReportViewer1.ViewReport();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static ADODB.Recordset getRSreport(ref string sql)
{
ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
string Path = null;
string strDBPath = null;
// ERROR: Not supported in C#: OnErrorStatement
functionReturnValue = new ADODB.Recordset();
functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
Debug.Print(sql);
functionReturnValue.Open(sql, cnnDBreport, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
return functionReturnValue;
getRSreport_Error:
if (cnnDBreport == null) {
Interaction.MsgBox(Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " cnnDBreport object has not been made.");
} else if (Err().Description == "Not a valid password.") {
Interaction.MsgBox("Error while getRSreport and Error is :" + Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + cnnDBreport.ConnectionString + Constants.vbCrLf + Constants.vbCrLf + " --- " + cnnDBreport.State);
modRecordSet.cnnDB.Close();
//UPGRADE_NOTE: Object cnnDB may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
modRecordSet.cnnDB = null;
modRecordSet.cnnDB = new ADODB.Connection();
//UPGRADE_WARNING: Couldn't resolve default property of object strDBPath. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
strDBPath = modRecordSet.serverPath + "pricing.mdb";
//UPGRADE_WARNING: Couldn't resolve default property of object strDBPath. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
//UPGRADE_WARNING: Couldn't resolve default property of object Path. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
Path = strDBPath + ";Jet " + "OLEDB:Database Password=lqd";
//cnnDB.CursorLocation = adUseClient
//UPGRADE_WARNING: Couldn't resolve default property of object Path. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
modRecordSet.cnnDB.Open("Provider=Microsoft.ACE.OLEDB.12.0;Mode=Share Deny Read|Share Deny Write;Persist Security Info=False;Data Source= " + Path);
modRecordSet.cnnDB.Execute("ALTER DATABASE PASSWORD Null " + " " + "lqd");
modRecordSet.cnnDB.Close();
//UPGRADE_NOTE: Object cnnDB may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
modRecordSet.cnnDB = null;
modRecordSet.openConnection();
} else {
Interaction.MsgBox("Error while getRSreport and Error is :" + Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + cnnDBreport.ConnectionString + Constants.vbCrLf + Constants.vbCrLf + " --- " + cnnDBreport.State);
}
// ERROR: Not supported in C#: ResumeStatement
return functionReturnValue;
}
public customer getCustomer(ref int id)
{
string sql = null;
ADODB.Connection cn = default(ADODB.Connection);
ADODB.Recordset rs = default(ADODB.Recordset);
customer customer_Renamed = null;
if (id) {
cn = modRecordSet.openConnectionInstance();
if (cn == null) {
Interaction.MsgBox("You are not connected to the 4POS BackOffice server! This task can only be performed if you are connected to the server. Please contact your System Administrator.", MsgBoxStyle.Exclamation, "BackOffice Connection Error");
} else {
rs = new ADODB.Recordset();
sql = "SELECT Customer.*, [Customer_Current]+[Customer_30Days]+[Customer_60Days]+[Customer_90Days]+[Customer_120Days]+[Customer_150Days] AS balance From Customer WHERE CustomerID=" + id;
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
customer_Renamed = new customer();
customer_Renamed.Key = rs.Fields("CustomerID").Value;
customer_Renamed.channelID = rs.Fields("Customer_ChannelID").Value;
customer_Renamed.creditLimit = rs.Fields("Customer_CreditLimit").Value;
customer_Renamed.department = rs.Fields("Customer_DepartmentName").Value + "";
customer_Renamed.fax = rs.Fields("Customer_Fax").Value + "";
customer_Renamed.name = rs.Fields("Customer_InvoiceName").Value + "";
customer_Renamed.outstanding = rs.Fields("balance").Value;
customer_Renamed.person = rs.Fields("Customer_FirstName").Value + " " + rs.Fields("Customer_Surname").Value;
customer_Renamed.physical = rs.Fields("Customer_PhysicalAddress").Value + "";
customer_Renamed.postal = rs.Fields("Customer_PostalAddress").Value + "";
customer_Renamed.signed_Renamed = customer_Renamed.person;
customer_Renamed.telephone = rs.Fields("Customer_Telephone").Value + "";
customer_Renamed.terms = rs.Fields("Customer_Terms").Value;
customer_Renamed.tax = rs.Fields("Customer_VATNumber").Value + "";
}
}
}
return customer_Renamed;
}
public void ImportSalesOrder()
{
string soPath = "http://nwszmail/public/namwah/Sales/Timex/PurchaseOrders/";
string strSQL = "";
Dictionary<string, SalesOrder> dictSo = new Dictionary<string, SalesOrder>();
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rst = new ADODB.Recordset();
Item item;
/*
XPCollection<SalesOrderLine> soLines = new XPCollection<SalesOrderLine>(session);
session.BeginTransaction();
session.Delete(soLines);
session.CommitTransaction();
return;
*/
string partNo;
string soLineIndex;
CacheItem();
CacheSalesOrderLine(Namwah.Module.BO.SalesOrder.SalesOrderType.Normal);
ConnectDB(cnn, soPath);
strSQL = "SELECT \"nw:partno\" , \"nw:cpo:item:ignore\", \"nw:cpo:item:index\", \"nw:cpo:item:initneeddate\", \"nw:cpo:date\" ";
strSQL = strSQL + " , \"nw:cpo:item:needqty\", \"nw:cpo:item:needdate\", \"nw:cpo:item:priority\", \"nw:cpo:item:shippedqty\" ";
strSQL = strSQL + " , \"nw:cpo:item:remark\", \"nw:cpo:no\", \"nw:customer\", \"nw:part:tmxrefno\" ";
strSQL = strSQL + " FROM \"" + soPath;
strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false AND \"nw:customer\" = 'Timex' )";
return ;
rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);
session.BeginTransaction();
DateTime sTime = DateTime.Now;
string poNo;
while (!rst.EOF)
{
partNo = rst.Fields["nw:partno"].Value.ToString();
soLineIndex = rst.Fields["nw:cpo:no"].Value.ToString() + "-" + rst.Fields["nw:cpo:item:index"].Value.ToString();
poNo = rst.Fields["nw:cpo:no"].Value.ToString();
if (poNo.StartsWith("PoHistory") == false)
{
if (dictItem.ContainsKey(partNo))
{
if (dictSalesOrderLine.ContainsKey(soLineIndex))
{
SalesOrderLine soLine = dictSalesOrderLine[soLineIndex];
soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
soLine.SetShipQty(float.Parse(rst.Fields["nw:cpo:item:shippedqty"].Value.ToString()));
soLine.Save();
}
else
{
item = dictItem[partNo];
SalesOrder so;
if (dictSo.ContainsKey(rst.Fields["nw:cpo:no"].Value.ToString()))
{
so = dictSo[rst.Fields["nw:cpo:no"].Value.ToString()];
}
else
{
so = session.FindObject<SalesOrder>(new BinaryOperator("OrderNo", rst.Fields["nw:cpo:no"].Value.ToString()));
if (so == null)
{
so = new SalesOrder(session);
so.SetOrderNo(rst.Fields["nw:cpo:no"].Value.ToString());
so.Customer = GetCustomer(rst.Fields["nw:customer"].Value.ToString());
so.Save();
}
dictSo.Add(rst.Fields["nw:cpo:no"].Value.ToString(), so);
}
SalesOrderLine soLine = new SalesOrderLine(session);
soLine.SalesOrder = so;
soLine.Item = item;
soLine.SetOrderLineNo(int.Parse(rst.Fields["nw:cpo:item:index"].Value.ToString()));
soLine.CustomerItemNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
soLine.CustomerOrderNo = rst.Fields["nw:cpo:no"].Value.ToString();
if (rst.Fields["nw:cpo:item:ignore"].Value.ToString() != "")
soLine.IgnorePlanningReport = bool.Parse(rst.Fields["nw:cpo:item:ignore"].Value.ToString());
if (rst.Fields["nw:cpo:item:initneeddate"].Value.ToString() != "")
soLine.InitNeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:initneeddate"].Value.ToString());
soLine.NeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:needdate"].Value.ToString());
soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
soLine.PoDate = DateTime.Parse(rst.Fields["nw:cpo:date"].Value.ToString());
soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
soLine.SetShipQty(float.Parse(rst.Fields["nw:cpo:item:shippedqty"].Value.ToString()));
soLine.ApprovedSalesOrderLine();
soLine.Save();
}
}
}
rst.MoveNext();
}
System.Diagnostics.Debug.WriteLine("Start Time : " + sTime.ToString() + ". End Time : " + DateTime.Now.ToString());
session.CommitTransaction();
System.Diagnostics.Debug.WriteLine("Commit Time : " + DateTime.Now.ToString());
}
public void ImportWorkOrder()
{
string woPath = "http://nwszmail/public/namwah/WorkOrders/";
string strSQL = "";
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rst = new ADODB.Recordset();
Item item;
WorkOrder wo;
string partNo;
string woNo;
session.BeginTransaction();
CacheItem();
CacheWo();
ConnectDB(cnn, woPath);
strSQL = "SELECT \"nw:partno\" , \"nw:partname\", \"nw:parttype\", \"nw:wo:no\", ";
strSQL = strSQL + " \"nw:wo:needqty\", \"nw:wo:needdate\", \"nw:wo:ssblankuseqty\", \"nw:wo:extra\" ";
strSQL = strSQL + " FROM \"" + woPath;
strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false)";
rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);
while (!rst.EOF)
{
partNo = rst.Fields["nw:partno"].Value.ToString();
woNo = rst.Fields["nw:wo:no"].Value.ToString();
if (dictItem.ContainsKey(partNo))
{
if (dictWo.ContainsKey(woNo))
{
WorkOrder oldWo = dictWo[woNo];
if (oldWo.NeedQty != float.Parse(rst.Fields["nw:wo:needqty"].Value.ToString()))
{
oldWo.NeedQty = float.Parse(rst.Fields["nw:wo:needqty"].Value.ToString());
oldWo.Save();
}
}
else
{
item = dictItem[partNo];
wo = new WorkOrder(session, woNo);
wo.Item = item;
wo.NeedDate = DateTime.Parse(rst.Fields["nw:wo:needdate"].Value.ToString());
wo.NeedQty = float.Parse(rst.Fields["nw:wo:needqty"].Value.ToString());
if (rst.Fields["nw:wo:extra"].Value.ToString() != "")
wo.ExtraQty = float.Parse(rst.Fields["nw:wo:extra"].Value.ToString());
if (rst.Fields["nw:wo:ssblankuseqty"].Value.ToString() != "")
wo.ssBlankUseQty = float.Parse(rst.Fields["nw:wo:ssblankuseqty"].Value.ToString());
wo.Save();
}
}
rst.MoveNext();
}
session.CommitTransaction();
}
public void ImportSSTK()
{
string soPath = "http://nwszmail/public/namwah/Parts2/SafetyStock/";
string strSQL = "";
Dictionary<string, SalesOrder> dictSo = new Dictionary<string, SalesOrder>();
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rst = new ADODB.Recordset();
Item item;
string partNo;
string soLineIndex;
CacheItem();
ConnectDB(cnn, soPath);
strSQL = "SELECT \"nw:cpo:item:initneeddate\", \"nw:cpo:date\" ";
strSQL = strSQL + " , \"nw:cpo:item:needqty\", \"nw:cpo:item:needdate\", \"nw:cpo:item:priority\", \"nw:part:sstqty\" ";
strSQL = strSQL + " , \"nw:cpo:no\", \"nw:part:tmxrefno\" ";
strSQL = strSQL + " FROM \"" + soPath;
strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false )";
rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);
session.BeginTransaction();
XPCollection<SalesOrderLine> soLines = new XPCollection<SalesOrderLine>(session);
soLines.Criteria = CriteriaOperator.Parse("SalesOrder.OrderType == SalesOrder.SalesOrderType.SSTK");
session.BeginTransaction();
session.Delete(soLines);
session.CommitTransaction();
DateTime sTime = DateTime.Now;
string poNo;
while (!rst.EOF)
{
partNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
soLineIndex = rst.Fields["nw:cpo:no"].Value.ToString() + "-" + rst.Fields["nw:part:tmxrefno"].Value.ToString();
poNo = rst.Fields["nw:cpo:no"].Value.ToString();
if (dictItem.ContainsKey(partNo))
{
item = dictItem[partNo];
SalesOrder so;
if (dictSo.ContainsKey(rst.Fields["nw:cpo:no"].Value.ToString()))
{
so = dictSo[rst.Fields["nw:cpo:no"].Value.ToString()];
}
else
{
so = session.FindObject<SalesOrder>(new BinaryOperator("OrderNo", rst.Fields["nw:cpo:no"].Value.ToString()));
if (so == null)
{
so = new SalesOrder(session);
so.SetOrderNo(rst.Fields["nw:cpo:no"].Value.ToString());
so.OrderType = SalesOrder.SalesOrderType.SSTK;
so.Customer = GetCustomer("TIMEX");
so.Save();
}
dictSo.Add(rst.Fields["nw:cpo:no"].Value.ToString(), so);
}
SalesOrderLine soLine = new SalesOrderLine(session);
soLine.SalesOrder = so;
soLine.Item = item;
so.AddLineNo();
soLine.SetOrderLineNo(so.LastLineNo);
soLine.CustomerItemNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
soLine.CustomerOrderNo = rst.Fields["nw:cpo:no"].Value.ToString();
//soLine.InitNeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:initneeddate"].Value.ToString());
soLine.NeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:needdate"].Value.ToString());
soLine.NeedQty = float.Parse(rst.Fields["nw:part:sstqty"].Value.ToString());
soLine.PoDate = DateTime.Parse(rst.Fields["nw:cpo:date"].Value.ToString());
soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
soLine.ApprovedSalesOrderLine();
soLine.Save();
}
rst.MoveNext();
}
System.Diagnostics.Debug.WriteLine("Start Time : " + sTime.ToString() + ". End Time : " + DateTime.Now.ToString());
session.CommitTransaction();
System.Diagnostics.Debug.WriteLine("Commit Time : " + DateTime.Now.ToString());
}
private void frmBarcodeStockitem_Load(System.Object eventSender, System.EventArgs eventArgs)
{
int x = 0;
string lOrder = null;
string sql = null;
string stSring = null;
ADODB.Recordset rsPrinter_B = new ADODB.Recordset();
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
TextBox tb = new TextBox();
txtSearch.AddRange(new TextBox[] {
_txtSearch_0,
_txtSearch_1,
_txtSearch_2,
_txtSearch_3,
_txtSearch_4
});
foreach (TextBox tb_loopVariable in txtSearch) {
tb = tb_loopVariable;
tb.TextChanged += txtSearch_TextChanged;
tb.Enter += txtSearch_Enter;
tb.KeyDown += txtSearch_KeyDown;
tb.KeyPress += txtSearch_KeyPress;
tb.Leave += txtSearch_Leave;
}
modBResolutions.ResizeForm(ref this, ref sizeConvertors.pixelToTwips(this.Width, true), ref sizeConvertors.pixelToTwips(this.Height, false), ref 0);
// ERROR: Not supported in C#: OnErrorStatement
//Doing shelf from file
if (modApplication.grvPrin) {
//rsPrinter_B.Close
rsPrinter_B.Open(modRecordSet.serverPath + "ShelfBarcode.dat");
//rsPrinter_B.filter = ""
//If frmBarcode._optBarcode_2.value = True Then rsPrinter_B.filter = "StockItem_SBarcode ='barcode'"
//If frmBarcode._optBarcode_1.value = True Then rsPrinter_B.filter = "StockItem_SBarcode ='shelf'"
if (My.MyProject.Forms.frmBarcode._optBarcode_2.Checked == true)
rsPrinter_B.filter = "StockItem_SBarcode = true";
if (My.MyProject.Forms.frmBarcode._optBarcode_1.Checked == true)
rsPrinter_B.filter = "StockItem_SShelf =true";
//If grvPrinType = 1 Then rsPrinter_B.filter = "StockItem_SBarcode ='shelf'"
modRecordSet.cnnDB.Execute("DELETE * FROM barcodePersonLnk");
while (!(rsPrinter_B.EOF)) {
//If its a shelf talker.....
if (My.MyProject.Forms.frmBarcode._optBarcode_1.Checked == true) {
sql = "INSERT INTO barcodePersonLnk ( barcodePersonLnk_PersonID, barcodePersonLnk_StockItemID, barcodePersonLnk_Shrink,barcodePersonLnk_PrintQTY ) ";
sql = sql + "SELECT theJoin.Person, theJoin.Catalogue_StockItemID, theJoin.Catalogue_Quantity , theJoin.PrinQTY FROM (SELECT Catalogue.Catalogue_StockItemID, Catalogue.Catalogue_Quantity, " + modRecordSet.gPersonID + " AS Person, 1 AS PrinQTY FROM Catalogue WHERE Catalogue.Catalogue_Quantity = 1 AND Catalogue_StockItemID = " + rsPrinter_B.Fields("GRVItem_StockItemID").Value + ") AS theJoin";
} else {
sql = "INSERT INTO barcodePersonLnk ( barcodePersonLnk_PersonID, barcodePersonLnk_StockItemID, barcodePersonLnk_Shrink,barcodePersonLnk_PrintQTY ) ";
sql = sql + "SELECT theJoin.Person, theJoin.Catalogue_StockItemID, theJoin.Catalogue_Quantity , theJoin.PrinQTY FROM (SELECT Catalogue.Catalogue_StockItemID, Catalogue.Catalogue_Quantity, " + modRecordSet.gPersonID + " AS Person, " + rsPrinter_B.Fields("GRVItem_Quantity").Value + " AS PrinQTY FROM Catalogue WHERE Catalogue.Catalogue_Quantity = 1 AND Catalogue_StockItemID = " + rsPrinter_B.Fields("GRVItem_StockItemID").Value + ") AS theJoin";
}
#if DEBUG
Debug.Print(sql);
#endif
modRecordSet.cnnDB.Execute(sql);
rsPrinter_B.moveNext();
}
} else {
modRecordSet.cnnDB.Execute("DELETE * FROM barcodePersonLnk");
sql = "INSERT INTO barcodePersonLnk ( barcodePersonLnk_PersonID, barcodePersonLnk_StockItemID, barcodePersonLnk_Shrink ) ";
sql = sql + "SELECT theJoin.Person, theJoin.Catalogue_StockItemID, theJoin.Catalogue_Quantity ";
sql = sql + "FROM (SELECT Catalogue.Catalogue_StockItemID, Catalogue.Catalogue_Quantity, " + modRecordSet.gPersonID + " AS Person FROM Catalogue) AS theJoin LEFT JOIN barcodePersonLnk ON (theJoin.Person = barcodePersonLnk.barcodePersonLnk_PersonID) AND (theJoin.Catalogue_Quantity = barcodePersonLnk.barcodePersonLnk_Shrink) AND (theJoin.Catalogue_StockItemID = barcodePersonLnk.barcodePersonLnk_StockItemID) WHERE (((barcodePersonLnk.barcodePersonLnk_PersonID) Is Null));";
modRecordSet.cnnDB.Execute(sql);
}
int lLeft = 0;
var _with3 = gridEdit;
_with3.Col = 7;
_with3.RowCount = 0;
System.Windows.Forms.Application.DoEvents();
_with3.RowCount = 2;
_with3.FixedRows = 1;
_with3.FixedCols = 0;
_with3.row = 0;
_with3.Col = 0;
_with3.CellFontBold = true;
_with3.Text = "Barcode";
_with3.set_ColWidth(0, 1400);
_with3.CellAlignment = 7;
_with3.Col = 1;
_with3.CellFontBold = true;
_with3.Text = "ID";
_with3.set_ColWidth(1, 800);
_with3.CellAlignment = 4;
_with3.Col = 2;
_with3.CellFontBold = true;
_with3.Text = "Stock Name";
_with3.set_ColWidth(2, 2000);
_with3.CellAlignment = 1;
_with3.Col = 3;
_with3.CellFontBold = true;
_with3.Text = "Supplier";
_with3.set_ColWidth(3, 2000);
_with3.CellAlignment = 1;
_with3.Col = 4;
_with3.CellFontBold = true;
_with3.Text = "Department";
_with3.set_ColWidth(4, 2000);
_with3.CellAlignment = 1;
_with3.Col = 5;
_with3.CellFontBold = true;
_with3.Text = "QTY";
_with3.set_ColWidth(5, 800);
_with3.CellAlignment = 1;
_with3.Col = 6;
_with3.CellFontBold = true;
_with3.Text = "Price Status";
_with3.set_ColWidth(6, 1300);
_with3.CellAlignment = 2;
lOrder = " ORDER BY StockItem.StockItem_Name";
lLeft = sizeConvertors.pixelToTwips(_with3.Left, true);
for (x = 0; x <= txtSearch.Count; x++) {
txtSearch[x].Left = sizeConvertors.twipsToPixels(lLeft, true);
txtSearch[x].Width = sizeConvertors.twipsToPixels(_with3.get_ColWidth(x), true);
lLeft = lLeft + sizeConvertors.pixelToTwips(txtSearch[x].Width, true);
}
_with3.Col = 0;
doRS(ref ref true);
//doRS True
doRS();
return;
ErrLoadForm:
if (Err().Number == 0) {
// ERROR: Not supported in C#: ResumeStatement
#if DEBUG
} else {
Debug.Print(Err().Number);
#endif
Interaction.MsgBox(Err().Number + Err().Description);
// ERROR: Not supported in C#: ResumeStatement
}
}
public void ImportHKIPO()
{
string ipoPath = "http://nwszmail/public/namwah/WorkOrders/Purchasing/InternalPurchaseOrders";
string strSQL = "";
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rst = new ADODB.Recordset();
Item item;
PurchOrderLine poLine;
WorkOrder wo;
string partNo;
string woNo;
string ipoNo;
session.BeginTransaction();
CacheItem();
CacheWo();
CacheIpo();
ConnectDB(cnn, ipoPath);
strSQL = "SELECT \"nw:partno\" , \"nw:partname\", \"nw:parttype\", \"nw:wo:no\", \"nw:supplier\", ";
strSQL = strSQL + " \"nw:jo:needqty\", \"nw:jo:needdate\", \"nw:jo:arrivedqty\", \"nw:jo:no\" ";
strSQL = strSQL + " FROM \"" + ipoPath;
strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false)";
rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);
while (!rst.EOF)
{
partNo = rst.Fields["nw:partno"].Value.ToString();
woNo = rst.Fields["nw:wo:no"].Value.ToString();
ipoNo = rst.Fields["nw:jo:no"].Value.ToString();
if (dictPoLine.ContainsKey(ipoNo))
{
PurchOrderLine oldPoLine = dictPoLine[ipoNo];
if (oldPoLine.NeedQty != float.Parse(rst.Fields["nw:jo:needqty"].Value.ToString()) ||
oldPoLine.ReceivedQty != float.Parse(rst.Fields["nw:jo:arrivedqty"].Value.ToString()))
{
oldPoLine.NeedQty = float.Parse(rst.Fields["nw:jo:needqty"].Value.ToString());
oldPoLine.SetReceivedQty(float.Parse(rst.Fields["nw:jo:arrivedqty"].Value.ToString()));
oldPoLine.Save();
}
}
else
{
if (dictItem.ContainsKey(partNo) && dictWo.ContainsKey(woNo))
{
item = dictItem[partNo];
wo = dictWo[woNo];
poLine = new PurchOrderLine(session);
poLine.WorkOrder = wo;
poLine.Item = item;
poLine.SetPurchOrderLineNo(ipoNo);
poLine.NeedDate = DateTime.Parse(rst.Fields["nw:jo:needdate"].Value.ToString());
poLine.NeedQty = float.Parse(rst.Fields["nw:jo:needqty"].Value.ToString());
poLine.Vendor = GetVendor(rst.Fields["nw:supplier"].Value.ToString());
poLine.SetReceivedQty(float.Parse(rst.Fields["nw:jo:arrivedqty"].Value.ToString()));
poLine.Unit = item.Unit;
poLine.Warehouse = item.Warehouse;
poLine.Save();
}
}
rst.MoveNext();
}
session.CommitTransaction();
}
///<summary>
/// Execute a SQL command
/// <param name="SQLText">
/// SQL command to execute
/// </param>
/// <param name="rows">
/// Maximum number of row to extract. If is "0" then all rows are extracted.
/// </param>
/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
///or a <see cref="System.Data.DataSet">DataSet</see> object.
/// </returns>
/// </summary>
public override object ExecuteSQL(string SQLText, int rows)
{
ADODB.Recordset record = new ADODB.Recordset();
DataTable table = null;
this.pADOConnection.BeginTrans();
try
{
record.MaxRecords = rows;
record.Open((object)SQLText,
(object)this.pADOConnection,
ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockPessimistic,
(int)ADODB.CommandTypeEnum.adCmdText
);
table = RecordSetToDataTable(record);
}
catch (System.Exception)
{
this.pADOConnection.RollbackTrans();
string mes = SQLText + "\n\r";
foreach (ADODB.Error err in this.pADOConnection.Errors)
{
mes += "-----------------\n\r";
mes += err.Description + "\n\r";
mes += err.NativeError + "\n\r";
}
throw new ExecuteSQLException(mes);
}
finally
{
this.pADOConnection.CommitTrans();
}
return table;
}
private void printTransactionA4(ref transaction lTransaction)
{
string typeQuote = null;
string typeAccountPayment = null;
string typeConsignment = null;
string typeConsignmentReturn = null;
string typeAccountSaleCOD = null;
string typeAccountSale = null;
int lRetVal = 0;
int hkey = 0;
string vValue = null;
decimal lnVat = default(decimal);
string gPath = null;
ADODB.Recordset rs = default(ADODB.Recordset);
customer customer_Renamed = null;
string sql = null;
ADODB.Recordset rsNew = new ADODB.Recordset();
//Dim rsNew2 As New Recordset
lineItem lineitem_Renamed = null;
string lString = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
short lPaymentType = 0;
string[] lArray = null;
decimal TCurrency = default(decimal);
decimal QuoteTotal = default(decimal);
// ERROR: Not supported in C#: OnErrorStatement
//Set which invoice is required
string sPrintGST = null;
sPrintGST = "";
QuoteTotal = 0;
//lRetVal = RegOpenKeyEx(HKEY_LOCAL_MACHINE, "Software\4POS\pos", 0, KEY_QUERY_VALUE, hkey)
//lRetVal = QueryValueEx(hkey, "printGST", sPrintGST)
//RegCloseKey (hkey)
//If sPrintGST = "" Then sPrintGST = "0"
// If gParameters.A4Exclusive = True Then
// Set Report = New cryReceipt1
// ElseIf sPrintGST = "1" Then
// Set Report = New cryReceipt1
// Else
Report.Load("cryReceipt.rpt");
// End If
//lPaymentType = getPaymentType(lTransaction)
//If lPaymentType And typeAccountPayment Then
// printTransactionPaymentA4 lTransaction
// Exit Sub
//End If
//Dim lnVat As Currency
gPath = "C:\\4POS\\pos\\";
int lVatAmount = 0;
int lAmount = 0;
rsNew.Open(gPath + "data\\item.rs", , ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
//, adOpenStatic, adLockOptimistic
lnVat = 0;
//a = rsNew.RecordCount
//rsNew.Close
//rsNew2.source = rsNew.source
//rsNew("transactionItem_quantity").Type = rsNew("transactionItem_price").Type
//rsNew("transactionItem_quantity").DefinedSize = rsNew("transactionItem_price").DefinedSize
//rsNew2("transactionItem_quantity").Attributes = rsNew("transactionItem_price").Attributes
//rsNew2("transactionItem_quantity").NumericScale = rsNew("transactionItem_price").NumericScale
//rsNew2("transactionItem_quantity").Precision = rsNew("transactionItem_price").Precision
foreach (lineItem lineitem_Renamed_loopVariable in lTransaction.lineItems) {
lineitem_Renamed = lineitem_Renamed_loopVariable;
rsNew.AddNew();
if (lineitem_Renamed.revoke) {
} else {
rsNew.Fields("transactionItem_build").Value = lineitem_Renamed.build;
rsNew.Fields("transactionItem_code").Value = lineitem_Renamed.code;
rsNew.Fields("transactionItem_depositType").Value = lineitem_Renamed.depositType;
rsNew.Fields("transactionItem_id").Value = lineitem_Renamed.id;
rsNew.Fields("transactionItem_lineNo").Value = lineitem_Renamed.lineNo;
rsNew.Fields("transactionItem_name").Value = lineitem_Renamed.name;
rsNew.Fields("transactionItem_originalPrice").Value = lineitem_Renamed.originalPrice;
rsNew.Fields("transactionItem_price").Value = lineitem_Renamed.price;
rsNew.Fields("transactionItem_quantity").Value = lineitem_Renamed.quantity;
rsNew.Fields("transactionItem_receiptName").Value = Strings.LTrim(Strings.Mid(lineitem_Renamed.receiptName, Strings.InStr(1, lineitem_Renamed.receiptName, "x") + 1, Strings.Len(lineitem_Renamed.receiptName)));
//lineitem.receiptName
rsNew.Fields("transactionItem_reversal").Value = lineitem_Renamed.reversal;
rsNew.Fields("transactionItem_revoke").Value = lineitem_Renamed.revoke;
rsNew.Fields("transactionItem_set").Value = lineitem_Renamed.setBuild;
rsNew.Fields("transactionItem_shrink").Value = lineitem_Renamed.shrink;
rsNew.Fields("transactionItem_type").Value = lineitem_Renamed.transactionType;
rsNew.Fields("transactionItem_vat").Value = lineitem_Renamed.vat;
if (lineitem_Renamed.vat > 0)
lnVat = lnVat + ((lineitem_Renamed.quantity * lineitem_Renamed.price) - ((lineitem_Renamed.quantity * lineitem_Renamed.price) / (1 + (lineitem_Renamed.vat / 100))));
QuoteTotal = QuoteTotal + (lineitem_Renamed.quantity * lineitem_Renamed.price);
//If gParameters.A4Exclusive = True Then TCurrency = TCurrency + ((lineitem.quantity * lineitem.price) / (1 + (lineitem.vat) / 100))
//TCurrency = 0 'TCurrency + ((lineitem.quantity * lineitem.price) / (1 + (lineitem.vat) / 100))
rsNew.update();
}
}
//rsNew.MoveFirst
//vValue = rsNew("transactionItem_quantity")
vValue = "";
lRetVal = modUtilities.RegOpenKeyEx(modUtilities.HKEY_LOCAL_MACHINE, "Software\\4POS\\pos", 0, modUtilities.KEY_QUERY_VALUE, ref hkey);
lRetVal = modUtilities.QueryValueEx(hkey, "A4Items", ref vValue);
modUtilities.RegCloseKey(hkey);
if (string.IsNullOrEmpty(vValue))
vValue = "0";
if (vValue == "1") {
rsNew.Sort = "transactionItem_receiptName";
} else if (vValue == "0") {
}
Report.Database.Tables(1).SetDataSource(rsNew);
//Report.Database.Tables(2).SetDataSource rsNew, 3
Report.SetParameterValue("txtCompanyName", lTransaction.companyName);
Report.SetParameterValue("txtCompanyDetails1", lTransaction.heading1);
if (!string.IsNullOrEmpty(lTransaction.heading2)) {
Report.SetParameterValue("txtCompanyDetails2", lTransaction.heading2);
if (!string.IsNullOrEmpty(lTransaction.heading3)) {
Report.SetParameterValue("txtCompanyDetails3", lTransaction.heading3);
//If gParameters.tr_PrintInvoice = False Then
if (!string.IsNullOrEmpty(lTransaction.taxNumber)) {
Report.SetParameterValue("txtCompanyDetails4", "TAX NO :" + lTransaction.taxNumber);
}
//End If
} else {
//If gParameters.tr_PrintInvoice = False Then
if (!string.IsNullOrEmpty(lTransaction.taxNumber)) {
Report.SetParameterValue("txtCompanyDetails3", "TAX NO :" + lTransaction.taxNumber);
}
//End If
}
} else {
if (!string.IsNullOrEmpty(lTransaction.heading3)) {
Report.SetParameterValue("txtCompanyDetails2", lTransaction.heading3);
//If gParameters.tr_PrintInvoice = False Then
if (!string.IsNullOrEmpty(lTransaction.taxNumber)) {
Report.SetParameterValue("txtCompanyDetails3", "TAX NO :" + lTransaction.taxNumber);
}
//End If
} else {
//If gParameters.tr_PrintInvoice = False Then
if (!string.IsNullOrEmpty(lTransaction.taxNumber)) {
Report.SetParameterValue("txtCompanyDetails2", "TAX NO :" + lTransaction.taxNumber);
}
//End If
}
}
lString = lString + lTransaction.heading1 + Strings.Chr(13);
lString = lString + lTransaction.heading2 + Strings.Chr(13);
lString = lString + lTransaction.heading3 + Strings.Chr(13);
if (!string.IsNullOrEmpty(lTransaction.taxNumber))
lString = lString + "TAX NO :" + lTransaction.taxNumber;
lString = Strings.Replace(lString, Strings.Chr(13) + Strings.Chr(13), Strings.Chr(13));
lString = Strings.Replace(lString, Strings.Chr(13) + Strings.Chr(13), Strings.Chr(13));
Report.SetParameterValue("txtInvoiceNumber", lTransaction.transactionID);
Report.SetParameterValue("txtInvoiceDate", Strings.Format(lTransaction.transactionDate, "dd mmm yyyy hh:mm"));
Report.SetParameterValue("txtPOS", lTransaction.posName);
Report.SetParameterValue("txtCashier", lTransaction.cashierName);
if (Strings.LCase(lTransaction.transactionType) == "deposit credit") {
Report.SetParameterValue("txtType", "DEPOSIT CREDIT");
Report.SetParameterValue("txtTypeSpecial", "");
Report.SetParameterValue("txtInvoiceNumber", "[" + lTransaction.transactionID + "]");
} else {
if (lTransaction.transactionSpecial_Renamed == null) {
if (lTransaction.customer_Renamed == null) {
Report.SetParameterValue("txtCustomer", "Cash Sale");
switch (lTransaction.paymentType) {
case Convert.ToString(1):
Report.SetParameterValue("txtCustomer", "Cash Sale");
break;
case Convert.ToString(2):
Report.SetParameterValue("txtCustomer", "Credit Card Sale");
break;
case Convert.ToString(3):
Report.SetParameterValue("txtCustomer", "Debit Card Sale");
break;
case Convert.ToString(4):
Report.SetParameterValue("txtCustomer", "Cheque Sale");
break;
case Convert.ToString(7):
Report.SetParameterValue("txtCustomer", "Split Tender");
break;
default:
Report.SetParameterValue("txtCustomer", "Cash Sale");
break;
}
Report.SetParameterValue("txtType", "TAX INVOICE");
Report.SetParameterValue("txtTypeSpecial", "");
} else {
Report.SetParameterValue("txtSigned", lTransaction.customer_Renamed.signed_Renamed);
Report.SetParameterValue("txtCustomer", lTransaction.customer_Renamed.name);
if (!string.IsNullOrEmpty(lTransaction.customer_Renamed.physical)) {
lArray = Strings.Split(lTransaction.customer_Renamed.physical, Constants.vbCrLf);
Report.SetParameterValue("txtCustAddress1", lArray[0]);
if (Information.UBound(lArray) >= 1)
Report.SetParameterValue("txtCustAddress2", lArray[1]);
if (Information.UBound(lArray) >= 2)
Report.SetParameterValue("txtCustAddress3", lArray[2]);
if (Information.UBound(lArray) >= 3)
Report.SetParameterValue("txtCustAddress4", lArray[3]);
}
if (!string.IsNullOrEmpty(lTransaction.customer_Renamed.tax))
Report.SetParameterValue("txtCustAddress5", "TAX NO: " + lTransaction.customer_Renamed.tax);
//Report.txtCustomerAddress.SetText lTransaction.customer.name & Chr(13) & Chr(13) & Replace(lTransaction.customer.physical, Chr(10), "") & Chr(13) & Chr(13) & "TAX NO: " & lTransaction.customer.tax
Report.SetParameterValue("txtType", "TAX INVOICE");
if (lTransaction.customer_Renamed.terms) {
Report.SetParameterValue("txtTypeSpecial", "Account Sale");
} else {
Report.SetParameterValue("txtTypeSpecial", "Cash Sale");
}
}
} else {
Report.SetParameterValue("txtType", "");
Report.SetParameterValue("txtTypeSpecial", "");
if (lPaymentType & typeQuote) {
Report.SetParameterValue("txtType", "QUOTE");
if (lPaymentType & typeConsignment) {
if (lPaymentType & typeAccountSale | lPaymentType & typeAccountSaleCOD) {
Report.SetParameterValue("txtTypeSpecial", "Account Consignment");
} else {
Report.SetParameterValue("txtTypeSpecial", "Consignment");
}
} else if (lPaymentType & typeConsignmentReturn) {
if (lPaymentType & typeAccountSale | lPaymentType & typeAccountSaleCOD) {
Report.SetParameterValue("txtTypeSpecial", "Account Consignment Return Quote");
} else {
Report.SetParameterValue("txtTypeSpecial", "Consignment Return");
}
} else {
if (lPaymentType & typeAccountSale | lPaymentType & typeAccountSaleCOD) {
Report.SetParameterValue("txtTypeSpecial", "Account");
} else {
Report.SetParameterValue("txtTypeSpecial", "");
}
}
} else if (lPaymentType & typeConsignment) {
if (lPaymentType & typeAccountSale | lPaymentType & typeAccountSaleCOD) {
Report.SetParameterValue("txtType", "Account Consignment Sale");
} else {
Report.SetParameterValue("txtType", "Consignment Sale");
}
} else if (lPaymentType & typeConsignmentReturn) {
if (lPaymentType & typeAccountSale | lPaymentType & typeAccountSaleCOD) {
Report.SetParameterValue("txtType", "Account Consignment Return");
} else {
Report.SetParameterValue("txtType", "Consignment Return");
}
} else if (lPaymentType & typeAccountSale) {
Report.SetParameterValue("txtType", "Account Sale");
} else if (lPaymentType & typeAccountPayment) {
Report.SetParameterValue("txtType", "Account Payment");
}
if (lTransaction.customer_Renamed == null) {
if (!string.IsNullOrEmpty(lTransaction.transactionSpecial_Renamed.address)) {
lArray = Strings.Split(lTransaction.transactionSpecial_Renamed.address, Constants.vbCrLf);
Report.SetParameterValue("txtCustAddress1", lArray[0]);
if (Information.UBound(lArray) >= 1)
Report.SetParameterValue("txtCustAddress2", lArray[1]);
if (Information.UBound(lArray) >= 2)
Report.SetParameterValue("txtCustAddress3", lArray[2]);
if (Information.UBound(lArray) >= 3)
Report.SetParameterValue("txtCustAddress4", lArray[3]);
}
Report.SetParameterValue("txtSigned", lTransaction.transactionSpecial_Renamed.name);
Report.SetParameterValue("txtCustomer", lTransaction.transactionSpecial_Renamed.name);
} else {
if (!string.IsNullOrEmpty(lTransaction.customer_Renamed.tax))
lTransaction.customer_Renamed.physical = lTransaction.customer_Renamed.physical;
if (!string.IsNullOrEmpty(lTransaction.customer_Renamed.physical)) {
lArray = Strings.Split(lTransaction.customer_Renamed.physical, Constants.vbCrLf);
Report.SetParameterValue("txtCustAddress1", lArray[0]);
if (Information.UBound(lArray) >= 1)
Report.SetParameterValue("txtCustAddress2", lArray[1]);
if (Information.UBound(lArray) >= 2)
Report.SetParameterValue("txtCustAddress3", lArray[2]);
if (Information.UBound(lArray) >= 3)
Report.SetParameterValue("txtCustAddress4", lArray[3]);
}
Report.SetParameterValue("txtSigned.", lTransaction.customer_Renamed.signed_Renamed);
Report.SetParameterValue("txtCustomer", lTransaction.customer_Renamed.name);
}
}
}
//do report reference.....
if (!string.IsNullOrEmpty(lTransaction.CardRefer)) {
Report.SetParameterValue("txtCard", "Card Reference : " + lTransaction.CardRefer);
//ElseIf gParameters.CardRefer = True Then
// Report.txtCard.SetText "Card Reference : " & stCard
}
if (!string.IsNullOrEmpty(lTransaction.OrderRefer)) {
Report.SetParameterValue("txtOrder", "Order Reference : " + lTransaction.OrderRefer);
//ElseIf gParameters.OrderRefer = True Then
// Report.txtOrder.SetText "Order Reference : " & stOrder
}
if (!string.IsNullOrEmpty(lTransaction.SerialRefer)) {
Report.SetParameterValue("txtSerial", "Serial Reference : " + lTransaction.SerialRefer);
//ElseIf gParameters.SerialRefer = True Then
// Report.txtSerial.SetText "Serial Reference : " & stSerial
}
Report.SetParameterValue("txtDiscount", Strings.FormatNumber(lTransaction.paymentDiscount, 2));
//If gParameters.A4Exclusive = True Then
// Report.txtAText.SetText FormatNumber(TCurrency - lTransaction.paymentDiscount, 2)
//End If
//If frmMain.lblChange.Caption = "" Then frmMain.lblChange.Caption = "0.00"
if (lPaymentType & typeQuote) {
Report.SetParameterValue("txtTender", Strings.FormatNumber("0.00", 2));
} else {
Report.SetParameterValue("txtTender", Strings.FormatNumber(lTransaction.paymentTender, 2));
// lTransaction.paymentTender, 2)
}
Report.SetParameterValue("txtVAT", Strings.FormatNumber(lnVat, 2));
Report.SetParameterValue("txtChange", Strings.FormatNumber("0.00", 2));
//Report.txtTotal.SetText FormatNumber(lTransaction.paymentTotal, 2)
if (lPaymentType & typeQuote) {
//FIXED: it was calculating twice Report.txtTotal.SetText FormatNumber((QuoteTotal - lTransaction.paymentDiscount), 2)
Report.SetParameterValue("txtTotal", Strings.FormatNumber(QuoteTotal, 2));
//a = QuoteTotal
} else {
Report.SetParameterValue("txtTotal", Strings.FormatNumber(lTransaction.paymentTotal, 2));
}
if (lPaymentType & typeQuote | lPaymentType & typeConsignment | lPaymentType & typeConsignmentReturn | lPaymentType & typeAccountSale) {
Report.ReportDefinition.Sections("Section7").SectionFormat.EnableSuppress = true;
}
//New banking details
ADODB.Recordset rsCompBank = default(ADODB.Recordset);
rsCompBank = modRecordSet.getRS(ref "select * from Company;");
if (rsCompBank.RecordCount) {
if (Information.IsDBNull(rsCompBank.Fields("Company_BankName").Value)) {
} else {
Report.SetParameterValue("txtBankName", rsCompBank.Fields("Company_BankName"));
}
if (Information.IsDBNull(rsCompBank.Fields("Company_BranchName").Value)) {
} else {
Report.SetParameterValue("txtBranchName", rsCompBank.Fields("Company_BranchName"));
}
if (Information.IsDBNull(rsCompBank.Fields("Company_BranchCode").Value)) {
} else {
Report.SetParameterValue("txtBranchCode", rsCompBank.Fields("Company_BranchCode"));
}
if (Information.IsDBNull(rsCompBank.Fields("Company_AccountNumber").Value)) {
} else {
Report.SetParameterValue("txtAccountNumber", rsCompBank.Fields("Company_AccountNumber"));
}
}
//...................
//Report.selectPrinter "", Printer.DeviceName, ""
//'Report.VerifyOnEveryPrint = True
//Report.PrintOut False
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
ptA4:
Interaction.MsgBox(Err().Number + Err().Description);
// ERROR: Not supported in C#: ResumeStatement
}
private void cmdLoad_Click(System.Object eventSender, System.EventArgs eventArgs)
{
string sql = null;
ADODB.Connection lConn = default(ADODB.Connection);
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsPurchase = default(ADODB.Recordset);
ADODB.Recordset rsSales = default(ADODB.Recordset);
ADODB.Recordset rsStock = default(ADODB.Recordset);
ADODB.Recordset rsCompany = default(ADODB.Recordset);
//Dim Report As New cryIncomeExpense
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryIncomeExpense.rpt");
//UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"'
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
if (cmbMonthEnd.SelectedIndex) {
lConn = modRecordSet.openConnectionInstance(ref "month" + gMonth - cmbMonthEnd.SelectedIndex + ".mdb");
if (lConn == null)
return;
rsStock = new ADODB.Recordset();
rsStock.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
sql = "SELECT DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]+[DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS total, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]) AS opening, Sum([DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]) AS sales, Sum([DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]) AS shrink, Sum([DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS grv From DayEndStockItemLnk GROUP BY DayEndStockItemLnk.DayEndStockItemLnk_DayEndID;";
rsStock.Open(sql, lConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
rsSales = new ADODB.Recordset();
rsSales.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
//UPGRADE_WARNING: Couldn't resolve default property of object sql. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
sql = "SELECT [M_DayEnd].[DayEndID], [M_DayEnd].[DayEnd_Date], Sum([Declaration].[Declaration_Total]) AS SumOfSale_Total FROM Declaration INNER JOIN M_DayEnd ON [Declaration].[Declaration_DayEndID]=[M_DayEnd].[DayEndID] GROUP BY [M_DayEnd].[DayEndID], [M_DayEnd].[DayEnd_Date];";
rsSales.Open(sql, lConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
} else {
modApplication.modUpdate = 3;
modApplication.updateStockMovement();
sql = "SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum(Declaration.Declaration_Total) AS SumOfSale_Total FROM Company, Declaration INNER JOIN DayEnd ON Declaration.Declaration_DayEndID = DayEnd.DayEndID Where (((DayEnd.DayEndID) <> [Company]![Company_DayEndID])) GROUP BY DayEnd.DayEndID, DayEnd.DayEnd_Date Union SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum([SaleItem_Price]*[SaleItem_Quantity]) AS SumOfSale_Total FROM Consignment AS Consignment_1 RIGHT JOIN (Consignment RIGHT JOIN ((Sale INNER JOIN (Company INNER JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID) ON Sale.Sale_DayEndID = DayEnd.DayEndID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleItem_SaleID) ON Consignment.Consignment_SaleID = Sale.SaleID) ON Consignment_1.Consignment_ReversalSaleID = Sale.SaleID Where (((SaleItem.SaleItem_Revoke) = 0)) GROUP BY Consignment.ConsignmentID, Consignment_1.ConsignmentID, DayEnd.DayEndID, DayEnd.DayEnd_Date ";
sql = sql + "HAVING (((Consignment.ConsignmentID) Is Null) AND ((Consignment_1.ConsignmentID) Is Null));";
rsSales = modRecordSet.getRS(ref sql);
rsStock = modRecordSet.getRS(ref "SELECT DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]+[DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS total, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]) AS opening, Sum([DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]) AS sales, Sum([DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]) AS shrink, Sum([DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS grv From DayEndStockItemLnk GROUP BY DayEndStockItemLnk.DayEndStockItemLnk_DayEndID;");
}
rsPurchase = modRecordSet.getRS(ref "SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum(grvPosted.GRV_InvoiceInclusive) AS SumOfGRV_InvoiceInclusive, ([MonthEnd_BudgetSales]/[MonthEnd_Days]) AS saleBudget, ([MonthEnd_BudgetPurchases]/[MonthEnd_Days]) AS purchaseBudget FROM [SELECT GRV.* From GRV WHERE (((GRV.GRV_GRVStatusID)=3))]. AS grvPosted RIGHT JOIN (DayEnd INNER JOIN MonthEnd ON DayEnd.DayEnd_MonthEndID = MonthEnd.MonthEndID) ON grvPosted.GRV_DayEndID = DayEnd.DayEndID Where (((MonthEnd.MonthEndID) = " + gMonth - cmbMonthEnd.SelectedIndex + ")) GROUP BY DayEnd.DayEndID, DayEnd.DayEnd_Date, ([MonthEnd_BudgetSales]/[MonthEnd_Days]), ([MonthEnd_BudgetPurchases]/[MonthEnd_Days]);");
rsCompany = modRecordSet.getRS(ref "SELECT MonthEnd.* From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth - cmbMonthEnd.SelectedIndex + "));");
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
//ReportNone.Load("cryNoRecords.rpt")
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ReportNone.Load("cryNoRecords.rpt");
if (rsPurchase.BOF | rsPurchase.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
//Report.Database.SetDataSource(rs)
Report.Database.Tables(1).SetDataSource(rsPurchase);
Report.Database.Tables(2).SetDataSource(rsSales);
Report.Database.Tables(3).SetDataSource(rsStock);
Report.Database.Tables(4).SetDataSource(rsCompany);
//UPGRADE_WARNING: Couldn't resolve default property of object Report.VerifyOnEveryPrint. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
//Report.VerifyOnEveryPrint = True
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
if (lConn == null) {
} else {
lConn.Close();
}
}
private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
BackgroundParameter bObj = e.Argument as BackgroundParameter;
string url = bObj.url;
string querySuffix = bObj.querySuffix;
Uri uriFujiRDS = new Uri(url);
ProgressObject pObj = new ProgressObject();
backgroundWorker.ReportProgress(0, pObj);
DateTime dtime = dateTimePickerStart.Value;
string dateStart = dtime.ToString("yyyy-MM-dd HH:mm:ss");
string dateEnd = dtime.AddHours(double.Parse(bObj.duration)).ToString("yyyy-MM-dd HH:mm:ss");
//dateTimePickerEnd.Value.ToString("yyyy-MM-dd HH:mm:ss");
string query = String.Format(@"select * from storage s,document d,study_document sd,study st,patient p,procedure_info pi
where d.id = sd.document_uid
and s.id=d.storage_uid
and st.id=sd.study_uid
and p.id=st.patient_uid
and pi.id=st.procedure_info_uid
and d.name='Notes'
and d.creation_timedate between to_date('{0}','YYYY-MM-DD HH24:MI:SS') and to_date('{1}','YYYY-MM-DD HH24:MI:SS')", dateStart, dateEnd);
query += querySuffix;
ADODB.Recordset rs = new ADODB.Recordset();
byte[] result = retrieveRDS(uriFujiRDS, query);
if (result == null) return;
string tempfile = Path.GetTempFileName();
ByteArrayToFile(tempfile, result);
rs.Open(tempfile, "Provider=MSPersist", ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
string notefile = Path.GetTempFileName();
//** Eliminating this code due to apparent unreliability of WebClient with redirects
//WebClient client = new WebClient();
//client.Credentials = myCredentialCache;
//client.UseDefaultCredentials = true;
dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("MRN", typeof(string));
dt.Columns.Add("Accession", typeof(string));
dt.Columns.Add("Procedure", typeof(string));
dt.Columns.Add("Study Time", typeof(string));
dt.Columns.Add("Note Time", typeof(string));
dt.Columns.Add("Note", typeof(string));
dt.Columns.Add("Reports", typeof(string));
DateTime lastprogress = DateTime.Now;
while (!rs.EOF)
{
string http_url = rs.Fields["http_url"].Value.ToString();
string https_url = rs.Fields["https_url"].Value.ToString();
string accnum = rs.Fields["ris_study_euid"].Value.ToString();
string proc = rs.Fields["description"].Value.ToString();
string proc_code = rs.Fields["code"].Value.ToString();
string filename = rs.Fields["filename"].Value.ToString();
string mrn = rs.Fields["internal_euid"].Value.ToString();
string lastname = rs.Fields["last_name"].Value.ToString();
string firstname = rs.Fields["first_name"].Value.ToString();
string middlename = rs.Fields["middle_name"].Value.ToString();
string study_time = ((DateTime)rs.Fields["study_timedate"].Value).ToString("s").Replace('T', ' ');
string creation_time = ((DateTime)rs.Fields["creation_timedate"].Value).ToString("s").Replace('T', ' ');
if (url.StartsWith("https"))
{
filename = https_url + filename;
}
else
{
filename = http_url + filename;
}
string note = "";
try
{
downloadFile(filename, notefile);
//client.DownloadFile(filename, notefile);
note = parseNote(notefile);
}
catch (Exception ex)
{
note = String.Format("Error downloading note at {0}: {1}", filename, ex.Message);
}
string name = (lastname + ", " + firstname + " " + middlename).Trim();
dt.Rows.Add(name, mrn, accnum, proc, study_time, creation_time, note, "");
rs.MoveNext();
pObj.dt = dt;
backgroundWorker.ReportProgress(0, pObj);
}
rs.Close();
File.Delete(tempfile);
int totalNotes = dt.Rows.Count;
int current = 0;
foreach (DataRow dr in dt.Rows)
{
// Retrieve reports here
query = String.Format(@"select * from storage s,document d,study_document sd,study st
where d.id = sd.document_uid
and s.id=d.storage_uid
and st.id=sd.study_uid
and d.name='Report'
and st.ris_study_euid='{0}'
order by d.creation_timedate", dr["Accession"]);
//dr["Reports"] = query;
result = retrieveRDS(uriFujiRDS, query);
if (result == null) return;
tempfile = Path.GetTempFileName();
ByteArrayToFile(tempfile, result);
rs.Open(tempfile, "Provider=MSPersist", ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
string report = "";
while (!rs.EOF)
{
string http_url = rs.Fields["http_url"].Value.ToString();
string https_url = rs.Fields["https_url"].Value.ToString();
string doctype = rs.Fields["name"].Value.ToString();
string filename = rs.Fields["filename"].Value.ToString();
if (url.StartsWith("https"))
{
filename = https_url + filename;
}
else
{
filename = http_url + filename;
}
try
{
downloadFile(filename, notefile);
//client.DownloadFile(filename, notefile);
report += parseReport(notefile, url);
}
catch (Exception ex)
{
report += String.Format("Error downloading report at {0}: {1}", filename, ex.Message);
}
rs.MoveNext();
if (!rs.EOF) report += "\r\n\r\n======\r\n\r\n";
}
rs.Close();
File.Delete(tempfile);
dr["Reports"] = report;
current += 1;
if ((DateTime.Now - lastprogress).Milliseconds > 50)
{
backgroundWorker.ReportProgress(current * 100 / totalNotes, pObj);
lastprogress = DateTime.Now;
}
}
File.Delete(notefile);
pObj.updateDT = true;
backgroundWorker.ReportProgress(100, pObj);
}
catch (Exception ex)
{
labelStatus.Text = ex.Message;
}
}
public void TestFillDataTableByNameWithADODBRecordSet()
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
DataSet stuDS = new DataSet();
DataTable stuTable = new DataTable("student");
stuTable.Columns.Add("id", typeof(int));
stuTable.Columns.Add("name", typeof(string));
stuDS.Tables.Add(stuTable);
//Use ADO objects from ADO library (msado15.dll) imported
// as.NET library ADODB.dll using TlbImp.exe
ADODB.Connection adoConn = new ADODB.Connection();
ADODB.Recordset adoRS = new ADODB.Recordset();
adoConn.Open("Provider=CUBRIDProvider;Location=test-db-server;Data Source=demodb;User Id=dba;Port=30000", "", "", -1);
adoRS.Open("SELECT * FROM student", adoConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, 1);
int fillRowCount = adapter.Fill(stuDS, adoRS, "student"); //This method implicitly calls Close on the ADO object when the fill operation is complete.
//adoRS.Close();
adoConn.Close();
Assert.AreEqual(3, fillRowCount);
Assert.AreEqual(3, stuTable.Rows.Count);
}
public void TestRefreshDataTableWithADODBRecordSet_Schema()
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet stuDS = new DataSet();
DataTable stuTable = new DataTable("student");
stuDS.Tables.Add(stuTable);
//Use ADO objects from ADO library (msado15.dll) imported
// as.NET library ADODB.dll using TlbImp.exe
ADODB.Connection adoConn = new ADODB.Connection();
ADODB.Recordset adoRS = new ADODB.Recordset();
adoConn.Open("Provider=CUBRIDProvider;Location=test-db-server;Data Source=demodb;User Id=dba;Port=30000", "", "", -1);
adoRS.Open("SELECT * FROM student", adoConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, 1);
adapter.Fill(stuTable, adoRS);
adoRS.Requery(0);
int refreshRowCount = adapter.Fill(stuTable, adoRS); // This method does not call Close on the ADO object when the fill operation is complete.
adoRS.Close();
adoConn.Close();
Assert.IsNotNull(stuTable.PrimaryKey);
Assert.AreEqual(3, refreshRowCount);
}
static void Main(string[] args)
{
ADODB.Connection conn = null;
ADODB.Recordset rs = null;
try
{
////////////////////////////////////////////////////////////////////////////////
// 连接数据源.
//
Console.WriteLine("正在连接数据库 ...");
// 获取连接字符串
string connStr = string.Format("Provider=SQLOLEDB;Data Source={0};Initial Catalog={1};Integrated Security=SSPI",
".\\sqlexpress", "SQLServer2005DB");
// 打开连接
conn = new ADODB.Connection();
conn.Open(connStr, null, null, 0);
////////////////////////////////////////////////////////////////////////////////
// 编写并执行ADO命令.
// 可以是SQL指令(SELECT/UPDATE/INSERT/DELETE),或是调用存储过程.
// 此处是一个INSERT命令示例.
//
Console.WriteLine("将一条记录插入表CountryRegion中...");
// 1. 生成一个Command对象
ADODB.Command cmdInsert = new ADODB.Command();
// 2. 将连接赋值于命令
cmdInsert.ActiveConnection = conn;
// 3. 设置命令文本
// SQL指令或者存储过程名
cmdInsert.CommandText = "INSERT INTO CountryRegion(CountryRegionCode, Name, ModifiedDate)"
+ " VALUES (?, ?, ?)";
// 4. 设置命令类型
// ADODB.CommandTypeEnum.adCmdText 用于普通的SQL指令;
// ADODB.CommandTypeEnum.adCmdStoredProc 用于存储过程.
cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;
// 5. 添加参数
// CountryRegionCode (nvarchar(20)参数的添加
ADODB.Parameter paramCode = cmdInsert.CreateParameter(
"CountryRegionCode", // 参数名
ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(20))
ADODB.ParameterDirectionEnum.adParamInput, // 参数类型
20, // 参数的最大长度
"ZZ"+DateTime.Now.Millisecond); // 参数值
cmdInsert.Parameters.Append(paramCode);
// Name (nvarchar(200))参数的添加
ADODB.Parameter paramName = cmdInsert.CreateParameter(
"Name", // 参数名
ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(200))
ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向
200, // 参数的最大长度
"Test Region Name"); // 参数值
cmdInsert.Parameters.Append(paramName);
// ModifiedDate (datetime)参数的添加
ADODB.Parameter paramModifiedDate = cmdInsert.CreateParameter(
"ModifiedDate", // 参数名
ADODB.DataTypeEnum.adDate, // 参数类型 (datetime)
ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向
-1, // 参数的最大长度 (datetime忽视该值)
DateTime.Now); // 参数值
cmdInsert.Parameters.Append(paramModifiedDate);
// 6. 执行命令
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
////////////////////////////////////////////////////////////////////////////////
// 使用Recordset对象.
// http://msdn.microsoft.com/en-us/library/ms681510.aspx
// Recordset表示了数据表中记录或执行命令获得的结果的集合。
// 在任何时候， Recordset对象都指向集合中的单条记录，并将
// 该记录作为它的当前记录。
//
Console.WriteLine("列出表CountryRegion中的所有记录");
// 1. 生成Recordset对象
rs = new ADODB.Recordset();
// 2. 打开Recordset对象
string strSelectCmd = "SELECT * FROM CountryRegion"; // WHERE ...
rs.Open(strSelectCmd, // SQL指令/表,视图名 /
// 存储过程调用 /文件名
conn, // 连接对象/连接字符串
ADODB.CursorTypeEnum.adOpenForwardOnly, // 游标类型. (只进游标)
ADODB.LockTypeEnum.adLockOptimistic, // 锁定类型. (仅当需要调用
// 更新方法时，才锁定记录）
(int)ADODB.CommandTypeEnum.adCmdText); // 将第一个参数视为SQL命令
// 或存储过程.
// 3. 通过向前移动游标列举记录
// 移动到Recordset中的第一条记录
rs.MoveFirst();
while (!rs.EOF)
{
// 当在表中定义了一个可空字段，需要检验字段中的值是否为DBNull.Value.
string code = (rs.Fields["CountryRegionCode"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["CountryRegionCode"].Value.ToString();
string name = (rs.Fields["Name"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["Name"].Value.ToString();
DateTime modifiedDate = (rs.Fields["ModifiedDate"].Value == DBNull.Value) ?
DateTime.MinValue : (DateTime)rs.Fields["ModifiedDate"].Value;
Console.WriteLine(" {2} \t{0}\t{1}", code, name, modifiedDate.ToString("yyyy-MM-dd"));
// 移动到下一条记录
rs.MoveNext();
}
}
catch (Exception ex)
{
Console.WriteLine("应用程序出现错误: {0}", ex.Message);
if (ex.InnerException != null)
Console.WriteLine("描述: {0}", ex.InnerException.Message);
}
finally
{
////////////////////////////////////////////////////////////////////////////////
// 退出前清理对象.
//
Console.WriteLine("正在关闭连接 ...");
// 关闭record set，当它处于打开状态时
if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)
rs.Close();
// 关闭数据库连接，当它处于打开状态时
if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)
conn.Close();
}
}
private void loadData()
{
string sql = null;
ADODB.Connection cn = default(ADODB.Connection);
ADODB.Recordset rs = new ADODB.Recordset();
short x = 0;
string tmp = null;
//If openConnection() Then
// frmMain.lblPath.Caption = serverPath
// closeConnection
//End If
cn = modRecordSet.openConnectionInstance();
if (cn == null) {
this.Close();
return;
} else {
sql = "SELECT Company.Company_MonthEndID FROM Company;";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
gMonthEnd = rs.Fields("Company_MonthEndID").Value;
cmbMonth.Items.Add(new LBI("This Month", gMonthEnd));
for (x = 2; x <= gMonthEnd; x++) {
tmp = (x - 1).ToString() + " Months Ago";
cmbMonth.Items.Add(new LBI(tmp, gMonthEnd - x + 1));
}
cmbMonth.SelectedIndex = 0;
cmbPOS.Items.Add(new LBI("[All Point Of Sale]", 0));
for (x = 1; x <= 12; x++) {
tmp = "Point of Sale " + x.ToString();
cmbPOS.Items.Add(new LBI(tmp, x));
}
cmbPOS.SelectedIndex = 0;
}
cmdsearch_Click_Payment();
cmdsearch_Click(cmdSearch, new System.EventArgs());
cmdsearch_Click_Alloc();
cmdAllocate.Focus();
}
private void loadCompanies()
{
string locationCompany_Name = null;
int locationCompanyID = 0;
int x = 0;
ADODB.Recordset rs = new ADODB.Recordset();
System.Windows.Forms.ListViewItem lListitem = null;
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
if (openConnection()) {
}
rs.Open("SELECT locationCompany.locationCompanyID, location.location_Name, locationCompany.locationCompany_Name, locationCompany.locationCompany_Path FROM location INNER JOIN locationCompany ON location.locationID = locationCompany.locationCompany_LocationID ORDER BY location.location_Name, locationCompany.locationCompany_Name;", cnnDBmaster, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
//Me.lvLocation.ListItems.Clear
if (rs.RecordCount) {
x = -1;
object[,] gArray = new object[rs.RecordCount, 3];
while (!(rs.EOF)) {
x = x + 1;
gArray[x, 0] = rs.Fields(locationCompanyID).Value;
gArray[x, 1] = rs.Fields(locationCompany_Name).Value + "";
gArray[x, 2] = 0;
rs.moveNext();
}
rs.Close();
//Do Until rs.EOF
// Set lListitem = lvLocation.ListItems.Add(, "k" & rs("locationCompanyID"), rs("locationCompany_Name"), , 2)
// lListitem.SubItems(1) = rs("location_Name") & ""
// lListitem.SubItems(2) = rs("locationCompany_Path") & ""
// If LCase(rs("locationCompany_Path") & "") = LCase(serverPath & "pricing.mdb") Then
// lListitem.Selected = True
// 'lvLocation_DblClick
// End If
// rs.moveNext
//Loop
}
}
private void cmdShowHistory_Click(System.Object eventSender, System.EventArgs eventArgs)
{
int i = 0;
int x = 0;
string sql = null;
string databaseName = null;
short y = 0;
short lMonth = 0;
ADODB.Connection cn = default(ADODB.Connection);
ADODB.Recordset rs = new ADODB.Recordset();
// ERROR: Not supported in C#: OnErrorStatement
if (cmdShowHistory.Text == "&Show Full History") {
cmdShowHistory.Text = "&Show Current Month";
} else {
cmdShowHistory.Text = "&Show Full History";
cmdsearch_Click(cmdSearch, new System.EventArgs());
return;
}
if (gLoading)
return;
gLoading = true;
Cursor = System.Windows.Forms.Cursors.WaitCursor;
System.Windows.Forms.Application.DoEvents();
y = cmbMonth.Items.Count - 1;
lvTransaction.Items.Clear();
lblcount.Text = "0 of 0";
lvTransaction.Visible = false;
string lPosString = null;
System.Windows.Forms.ListViewItem lvItem = null;
//(cmbMonth.ListCount - 1)
for (i = 0; i <= y; i++) {
lMonth = Convert.ToInt32(cmbMonth.Items[i]);
if (lMonth == gMonthEnd) {
databaseName = "pricing.mdb";
} else {
databaseName = "Month" + lMonth + ".mdb";
}
cn = modRecordSet.openConnectionInstance(ref databaseName);
if (cn == null) {
goto nextMonth;
//Exit Sub
}
//Dim lString As String
//Dim lCustomerString As String
//Dim lStockString As String
if (this.cmbPOS.SelectedIndex)
lPosString = " AND (Sale_PosID=" + cmbPOS.SelectedIndex + ")";
sql = "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + adoPrimaryRS.Fields("CustomerID").Value + ")) ORDER BY CustomerTransaction.CustomerTransactionID DESC;";
Debug.Print(sql);
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
x = 0;
//lvTransaction.Visible = False
while (!(rs.EOF)) {
x = x + 1;
if (gLoading) {
} else {
break; // TODO: might not be correct. Was : Exit Do
}
lblcount.Text = x + " of " + rs.RecordCount;
System.Windows.Forms.Application.DoEvents();
if (rs.Fields("CustomerTransaction_Reference").Value != "Month End") {
lvItem = lvTransaction.Items.Add("K" + rs.Fields("CustomerTransactionID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), "");
if (lvItem.SubItems.Count > 1) {
lvItem.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItem.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItem.SubItems.Count > 2) {
lvItem.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItem.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
if (lvItem.SubItems.Count > 3) {
lvItem.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItem.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItem.SubItems.Count > 4) {
lvItem.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItem.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
}
rs.moveNext();
}
//lvTransaction.Visible = True
rs.Close();
nextMonth:
}
lvTransaction.Visible = true;
Cursor = System.Windows.Forms.Cursors.Default;
gLoading = false;
return;
ErrShowHistory:
if (Strings.InStr(Strings.LCase(Err().Description), "not a valid path")) {
Interaction.MsgBox(Err().Number + " - " + Err().Description);
return;
} else {
Interaction.MsgBox(Err().Number + " - " + Err().Description);
return;
}
}
public ADODB.Recordset getRSMaster(ref string sql)
{
ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
functionReturnValue = new ADODB.Recordset();
functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
functionReturnValue.Open(sql, cnnDBmaster, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
return functionReturnValue;
}
private void cmdsearch_Click_AllocByID(ref int PId)
{
bool prPrevSerial_p = false;
int x = 0;
string sql = null;
string databaseName = null;
short y = 0;
short lMonth = 0;
ADODB.Connection cn = default(ADODB.Connection);
ADODB.Recordset rs = new ADODB.Recordset();
bSelectPayment = true;
if (gLoading)
return;
gLoading = true;
Cursor = System.Windows.Forms.Cursors.WaitCursor;
System.Windows.Forms.Application.DoEvents();
lMonth = Convert.ToInt16(cmbMonth.SelectedIndex);
if (lMonth == gMonthEnd) {
databaseName = "pricing.mdb";
} else {
databaseName = "Month" + lMonth + ".mdb";
}
//If openConnection() Then
// frmMain.lblPath.Caption = serverPath
// closeConnection
//End If
cn = modRecordSet.openConnectionInstance(ref databaseName);
if (cn == null) {
return;
}
string lString = null;
string lCustomerString = null;
string lStockString = null;
string lPosString = null;
if (this.cmbPOS.SelectedIndex)
lPosString = " AND (Sale_PosID=" + cmbPOS.SelectedIndex + ")";
lString = Strings.Trim(txtCustomer.Text);
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
//If lString <> "" Then
//lString = "(Customer_InvoiceName LIKE '%" & Replace(lString, " ", "%' AND Customer_InvoiceName LIKE '%") & "%')"
lString = "(CustomerID = " + adoPrimaryRS.Fields("CustomerID").Value + ")";
//Else
// lString = ""
//End If
lCustomerString = lString;
if (!string.IsNullOrEmpty(lCustomerString))
lCustomerString = " AND (" + lCustomerString + ") ";
//Cater for one at a time
if (!string.IsNullOrEmpty(Strings.Trim(this.txtStock.Text))) {
y = 1;
lString = Strings.Trim(this.txtStock.Text);
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
}
if (!string.IsNullOrEmpty(Strings.Trim(this.txtSerial.Text))) {
prPrevSerial_p = true;
y = 2;
lString = Strings.Trim(this.txtSerial.Text);
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
lString = Strings.Replace(lString, " ", " ");
}
if (!string.IsNullOrEmpty(lString)) {
//By StockItem........
if (y == 1)
lString = "(StockItem_Name LIKE '%" + Strings.Replace(lString, " ", "%' AND StockItem_Name LIKE '%") + "%')";
//By Serial Number......
if (y == 2)
lString = "(Sale.Sale_Serialref LIKE '" + Strings.Replace(lString, " ", "%' AND Sale.Sale_Serialref LIKE '%") + "%')";
} else {
lString = "";
}
lStockString = lString;
if (!string.IsNullOrEmpty(lStockString))
lStockString = " AND (" + lStockString + ") AND (SaleItem.SaleItem_DepositType=0)";
if (string.IsNullOrEmpty(lCustomerString)) {
if (string.IsNullOrEmpty(lStockString)) {
sql = "SELECT DISTINCT Sale.Sale_Reference, Sale.SaleID, Sale.Sale_PosID, Sale.Sale_DatePOS, Sale.Sale_Total, Sale.Sale_Tender, Sale.Sale_PaymentType From Sale Where ((Sale.Sale_PaymentType > 0) " + lPosString + " )ORDER BY Sale.SaleID DESC;";
} else {
//By StockItem........
if (y == 1)
sql = "SELECT DISTINCT Sale.Sale_Reference, Sale.SaleID, Sale.Sale_PosID, Sale.Sale_DatePOS, Sale.Sale_Total, Sale.Sale_Tender, Sale.Sale_PaymentType FROM (SaleItem INNER JOIN Sale ON SaleItem.SaleItem_SaleID = Sale.SaleID) INNER JOIN StockItem ON SaleItem.SaleItem_StockItemID = StockItem.StockItemID WHERE (((Sale.Sale_PaymentType) > 0) " + lStockString + lPosString + " ) ORDER BY Sale.SaleID DESC;";
//By Serial Number......
if (y == 2)
sql = "SELECT DISTINCT Sale.Sale_Reference, Sale.SaleID, Sale.Sale_PosID, Sale.Sale_DatePOS, Sale.Sale_Total, Sale.Sale_Tender, Sale.Sale_PaymentType FROM (SaleItem INNER JOIN Sale ON SaleItem.SaleItem_SaleID = Sale.SaleID) WHERE (((Sale.Sale_PaymentType) > 0) " + lStockString + lPosString + " ) ORDER BY Sale.SaleID DESC;";
}
} else {
if (string.IsNullOrEmpty(lStockString)) {
sql = "SELECT DISTINCT Sale.Sale_Reference, Sale.SaleID, Sale.Sale_PosID, Sale.Sale_DatePOS, Sale.Sale_Total, Sale.Sale_Tender, Sale.Sale_PaymentType FROM Customer INNER JOIN (CustomerTransaction INNER JOIN Sale ON CustomerTransaction.CustomerTransaction_ReferenceID = Sale.SaleID) ON Customer.CustomerID = CustomerTransaction.CustomerTransaction_CustomerID Where (((Sale.Sale_PaymentType) > 0) " + lCustomerString + lPosString + " ) ORDER BY Sale.SaleID DESC;";
} else {
sql = "SELECT DISTINCT Sale.Sale_Reference, Sale.SaleID, Sale.Sale_PosID, Sale.Sale_DatePOS, Sale.Sale_Total, Sale.Sale_Tender, Sale.Sale_PaymentType FROM (SaleItem INNER JOIN (Customer INNER JOIN (CustomerTransaction INNER JOIN Sale ON CustomerTransaction.CustomerTransaction_ReferenceID = Sale.SaleID) ON Customer.CustomerID = CustomerTransaction.CustomerTransaction_CustomerID) ON SaleItem.SaleItem_SaleID = Sale.SaleID) INNER JOIN StockItem ON SaleItem.SaleItem_StockItemID = StockItem.StockItemID Where (((Sale.Sale_PaymentType) > 0) " + lCustomerString + lStockString + lPosString + " ) ORDER BY Sale.SaleID DESC;";
sql = "SELECT DISTINCT Sale.Sale_Reference, Sale.SaleID, Sale.Sale_PosID, Sale.Sale_DatePOS, Sale.Sale_Total, Sale.Sale_Tender, Sale.Sale_PaymentType FROM StockItem, Customer INNER JOIN (CustomerTransaction INNER JOIN Sale ON CustomerTransaction.CustomerTransaction_ReferenceID = Sale.SaleID) ON Customer.CustomerID = CustomerTransaction.CustomerTransaction_CustomerID Where (((Sale.Sale_PaymentType) > 0) And ((Customer.CustomerID) = " + adoPrimaryRS.Fields("CustomerID").Value + ")) ORDER BY Sale.SaleID DESC;";
}
}
sql = "SELECT DISTINCT Sale.Sale_Reference, Sale.SaleID, Sale.Sale_PosID, Sale.Sale_DatePOS, Sale.Sale_Total, Sale.Sale_Tender, Sale.Sale_PaymentType FROM StockItem, Customer INNER JOIN (CustomerTransaction INNER JOIN Sale ON CustomerTransaction.CustomerTransaction_ReferenceID = Sale.SaleID) ON Customer.CustomerID = CustomerTransaction.CustomerTransaction_CustomerID Where (((Sale.Sale_PaymentType) > 0) And ((Customer.CustomerID) = " + adoPrimaryRS.Fields("CustomerID").Value + ")) ORDER BY Sale.SaleID DESC;";
sql = "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + adoPrimaryRS.Fields("CustomerID").Value + ")) ORDER BY CustomerTransaction.CustomerTransactionID DESC;";
sql = "SELECT CustomerTransactionAlloc.CustomerTransactionAllocID, CustomerTransactionAlloc.CustomerTransactionAlloc_MainID, CustomerTransactionAlloc.CustomerTransactionAlloc_Amount, CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, " + "CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated, TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransactionAlloc INNER JOIN (CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID) ON CustomerTransactionAlloc.CustomerTransactionAlloc_ChildID = CustomerTransaction.CustomerTransactionID WHERE (((CustomerTransactionAlloc.CustomerTransactionAlloc_MainID)=" + PId + "));";
Debug.Print(sql);
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
lvTransAlloc.Items.Clear();
lblcount.Text = "0 of 0";
System.Windows.Forms.ListViewItem lvItem = null;
x = 0;
lvTransAlloc.Visible = false;
while (!(rs.EOF)) {
x = x + 1;
if (gLoading) {
} else {
break; // TODO: might not be correct. Was : Exit Do
}
lblcount.Text = x + " of " + rs.RecordCount;
System.Windows.Forms.Application.DoEvents();
//If rs("CustomerTransaction_Reference") = "Month End" Then
if (rs.Fields("TransactionType_Name").Value == "B/Forward") {
if ((Information.IsDBNull(rs.Fields("debit").Value) ? 0 : rs.Fields("debit").Value) == rs.Fields("CustomerTransaction_Allocated").Value & (Information.IsDBNull(rs.Fields("credit").Value) ? 0 : rs.Fields("credit").Value) == 0) {
//Else
lvItem = lvTransAlloc.Items.Add("K" + rs.Fields("CustomerTransactionAllocID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), "");
if (lvItem.SubItems.Count > 1) {
lvItem.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItem.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItem.SubItems.Count > 2) {
lvItem.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItem.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
//Select Case rs("Sale_PaymentType")
// Case 1
// lvItem.SubItems(2) = "Cash"
// Case 2
// lvItem.SubItems(2) = "CR Card"
// Case 3
// lvItem.SubItems(2) = "DR Card"
// Case 4
// lvItem.SubItems(2) = "Cheque"
// Case 5
// lvItem.SubItems(2) = "Account"
// Case 7
// lvItem.SubItems(2) = "Split Tender"
// Case Else
// lvItem.SubItems(2) = "Cash"
//End Select
if (lvItem.SubItems.Count > 3) {
lvItem.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItem.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItem.SubItems.Count > 4) {
lvItem.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItem.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
if (lvItem.SubItems.Count > 5) {
lvItem.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransactionAlloc_Amount").Value, 4);
} else {
lvItem.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransactionAlloc_Amount").Value, 4)));
}
}
} else if (rs.Fields("TransactionType_Name").Value == "Invoice") {
//If IIf(IsNull(rs("debit")), 0, rs("debit")) = rs("CustomerTransaction_Allocated") Then
//Else
lvItem = lvTransAlloc.Items.Add("K" + rs.Fields("CustomerTransactionAllocID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), "");
if (lvItem.SubItems.Count > 1) {
lvItem.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItem.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItem.SubItems.Count > 2) {
lvItem.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItem.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
if (lvItem.SubItems.Count > 3) {
lvItem.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItem.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItem.SubItems.Count > 4) {
lvItem.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItem.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
if (lvItem.SubItems.Count > 5) {
lvItem.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransactionAlloc_Amount").Value, 4);
} else {
lvItem.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransactionAlloc_Amount").Value, 4)));
}
//End If
} else if (rs.Fields("TransactionType_Name").Value == "Interest") {
//If IIf(IsNull(rs("debit")), 0, rs("debit")) = rs("CustomerTransaction_Allocated") Then
//Else
lvItem = lvTransAlloc.Items.Add("K" + rs.Fields("CustomerTransactionAllocID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), "");
if (lvItem.SubItems.Count > 1) {
lvItem.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItem.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItem.SubItems.Count > 2) {
lvItem.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItem.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
if (lvItem.SubItems.Count > 3) {
lvItem.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItem.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItem.SubItems.Count > 4) {
lvItem.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItem.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
if (lvItem.SubItems.Count > 5) {
lvItem.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransactionAlloc_Amount").Value, 4);
} else {
lvItem.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransactionAlloc_Amount").Value, 4)));
}
//End If
} else if (rs.Fields("TransactionType_Name").Value == "Debit Journal") {
//If IIf(IsNull(rs("debit")), 0, rs("debit")) = rs("CustomerTransaction_Allocated") Then
//Else
lvItem = lvTransAlloc.Items.Add("K" + rs.Fields("CustomerTransactionAllocID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), "");
if (lvItem.SubItems.Count > 1) {
lvItem.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItem.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItem.SubItems.Count > 2) {
lvItem.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItem.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
if (lvItem.SubItems.Count > 3) {
lvItem.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItem.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItem.SubItems.Count > 4) {
lvItem.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItem.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
if (lvItem.SubItems.Count > 5) {
lvItem.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransactionAlloc_Amount").Value, 4);
} else {
lvItem.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransactionAlloc_Amount").Value, 4)));
}
//End If
}
rs.moveNext();
}
lvTransAlloc.Visible = true;
lvTransAlloc.Refresh();
Cursor = System.Windows.Forms.Cursors.Default;
gLoading = false;
}
public static void tstBrowser(string pth, string datSource, string steName, string tstName, int radnum, string baseURL, ref string[,] tstResult, out int fndExcep, out int fnlFail)
{
testname = tstName;
int vfyFunc;
int eleNum;
int maxCols;
int stpCount;
int itmCount;
int tstFail;
char chr = Convert.ToChar(34);
object[,] tmpArray;
string[] outArray;
string[] lstTest;
string[] dbArray;
string[,] dataArray;
string[,] rsltArray;
string argID;
string functionID;
string getNeg;
string nmFunc;
string step;
string strCon;
string strSQL;
string tstID;
string xlPath;
ADODB.Connection objCon;
ADODB.Recordset objRec;
TestSuite lstObject;
getNeg = "";
tstFail = 0;
fnlFail = 0;
fndExcep = 0;
DateTime tmp = DateTime.Now; //date-time at the time of test running
vfyFunc = 0;
rsltArray = new string[1, 6];
//open browser based on selection on the GUI
tstObject tstObj = new tstObject(radnum);
//TextFileOps.Write(pth, "<li>", 100);
switch (datSource)
{
case "DB":
{
//db connection string
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
//database connections objects
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
//open the connection to the database
objCon.Open(strCon);
//SQL to execute
strSQL = "SELECT id FROM test WHERE name = '" + tstName + "'";
//open recordset and get test id with SQL
objRec.Open(strSQL, objCon);
//set test id to a string variable
tmpArray = objRec.GetRows();
tstID = Convert.ToString(tmpArray[0, 0]);
//close the recordset
objRec.Close();
//SQL to execute
strSQL = "SELECT COUNT(*) FROM step WHERE test_id = '" + tstID + "'";
//open recordset and get the number of stepsto execute with SQL
objRec.Open(strSQL, objCon);
//get the count value from the recordset and convert to an int fpor use in the step loop
tmpArray = objRec.GetRows();
stpCount = Convert.ToInt32(tmpArray[0, 0]);
//close the recordset
objRec.Close();
//set up a for loop to run all steps in a test
for (int stp = 0; stp < stpCount; stp++)
{
if (stp == 23)
stp = 23;
step = "Step " + Convert.ToString(stp + 1);
//SQL to execute
strSQL = "SELECT function_id, argument_set_id FROM step WHERE (test_id = '" + tstID + "') AND (number = " + (stp + 1) + ")";
//open recordset and get all the ids necessary for this step
objRec.Open(strSQL, objCon);
tmpArray = objRec.GetRows();
//set the function id
functionID = Convert.ToString(tmpArray[0, 0]);
//set the hash string for the argument set
argID = Convert.ToString(tmpArray[1, 0]);
//close the recordset
objRec.Close();
//get the function name
strSQL = "SELECT function_name FROM function WHERE id = '" + functionID + "'";
objRec.Open(strSQL, objCon);
tmpArray = objRec.GetRows();
//set the function name variable
nmFunc = Convert.ToString(tmpArray[0, 0]);
//close the recordset
objRec.Close();
// get the number of argments to be set to the application
strSQL = "SELECT COUNT(*) FROM argument WHERE argument_set_id = '" + argID + "'";
objRec.Open(strSQL, objCon);
tmpArray = objRec.GetRows();
//set the itmCount varable
itmCount = Convert.ToInt32(tmpArray[0, 0]);
//close the recordset
objRec.Close();
if (itmCount > 0)
{
//set the array size for the inArray parameter of the drive function
dbArray = new string[itmCount];
//set the array with blank values
for (int x = 0; x < itmCount; x++)
dbArray[x] = String.Empty;
//get the argument data from the database and populate into the dbArray
strSQL = "SELECT value FROM argument WHERE argument_set_id = '" + argID + "' ORDER BY seq ASC";
objRec.Open(strSQL, objCon);
tmpArray = objRec.GetRows();
//close the recordset
objRec.Close();
for (int stpData = 0; stpData < tmpArray.Length; stpData++)
{
if (stpData != tmpArray.Length)
dbArray[stpData] = Convert.ToString(tmpArray[0, stpData]);
}
}
else
{
dbArray = new string[1];
dbArray[0] = "";
}
//call driveFunction to execute the step noted in dataArray[tstSuite. 0]
TextFileOps.Write(pth, "<li>", 100);
//initialize anm array to hold all of the test results that wiill be written to the results file
tstResult = new string[1, 9];
//pass the appropriate vars to the drivefunction
tstObj.driveFunction(tstObj, nmFunc, step, dbArray, baseURL, datSource, pth, ref getNeg, ref tstResult, out fndExcep, out tstFail);
for (int x = 0; x < tstResult.GetLength(0); x++)
{
TextFileOps.Write(pth, "<div>", 100);
if (fndExcep != -1)
{
if (tstResult[x, 0] == "verify")
{
switch (tstResult[x, 1].Trim())
{
case "button":
Recorder.btnVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], pth, getNeg);
break;
case "dropdown":
Recorder.dropdownListVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 5], tstResult[x, 3], pth, getNeg);
break;
case "field":
Recorder.fldVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg);
break;
case "grades":
Recorder.gradeVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], tstResult[x, 6], tstResult[x, 7], tstResult[x, 8], pth);
break;
case "image":
Recorder.imgVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg);
break;
case "link":
Recorder.lnkVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg);
break;
case "table":
Recorder.tblVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], pth, getNeg);
break;
case "text":
case "field text":
Recorder.txtVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg);
break;
case "outcome":
Recorder.outcomeVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], tstResult[x, 6], tstResult[x, 7], tstResult[x, 8], pth);
break;
case "percentage":
Recorder.pctVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], pth);
break;
}
}
else
{
TextFileOps.Write(pth, tstResult[x, 1].ToString(), Convert.ToInt32(tstResult[x, 2]));
}
}
else
{
TextFileOps.Write(pth, "<div>", 100);
TextFileOps.Write(pth, "Exception Found.............(" + step + ")", 80);
TextFileOps.Write(pth, "<br />", 100);
TextFileOps.Write(pth, tstResult[x, 1].ToString(), -1);
}
TextFileOps.Write(pth, "</div>", 100);
}
tstResult = null;
TextFileOps.Write(pth, "<br />", 100);
TextFileOps.Write(pth, "</li>", 100);
if (tstFail == -1)
fnlFail = -1;
if (fndExcep == -1)
{
break;
}
}
break;
}
case "EX":
{
//Initialize Firefox and construct the dataArray
//the out parameter is the maxColumns in what will be the fnlArray and hence the width of each item in the array
xlPath = TestSuite.getXlPath(steName);
lstObject = new TestSuite();
lstObject.getTestListing(out lstTest, xlPath);
dataArray = tstObj.xlFunctions(tstObj, xlPath, tstName, "Master", out maxCols);
//set eleNum (number of elements) to 0
for (int runStep = 0; runStep < dataArray.GetLength(0); runStep++)
{
eleNum = 0;
for (int stpNum = 3; stpNum < dataArray.GetLength(1); stpNum++)
{
//Get the number of elements in this step data from dataArray
if (dataArray[runStep, stpNum] != null)
{
eleNum++; //increment elenum in the presence of a value
} //in dataArray[tstSuite, stpNum]
else
{
break;
}
}
//initialize and set outArray with all data values in the dataArray line item
if (eleNum != 0)
{
outArray = new string[eleNum]; //set outArray to eleNum
//set the outarray with all data values or passing to driveFunction
for (int setArray = 0; setArray < eleNum; setArray++)
{
outArray[setArray] = dataArray[runStep, setArray + 3];
}
}
else
{
outArray = new string[1];
outArray[0] = "";
}
nmFunc = dataArray[runStep, 0];
step = dataArray[runStep, 2];
dbArray = outArray;
//call driveFunction to execute the step noted in dataArray[tstSuite. 0]
TextFileOps.Write(pth, "<li>", 100);
//initialize anm array to hold all of the test results that wiill be written to the results file
tstResult = new string[1, 9];
//pass the appropriate vars to the drivefunction
tstObj.driveFunction(tstObj, nmFunc, step, dbArray, baseURL, datSource, pth, ref getNeg, ref tstResult, out fndExcep, out tstFail);
for (int x = 0; x < tstResult.GetLength(0); x++)
{
TextFileOps.Write(pth, "<div>", 100);
if (fndExcep != -1)
{
if (tstResult[x, 0] == "verify")
switch (tstResult[x, 1].Trim())
{
case "button":
Recorder.btnVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], pth, getNeg);
break;
case "dropdown":
Recorder.dropdownListVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 5], tstResult[x, 3], pth, getNeg);
break;
case "field":
Recorder.fldVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg);
break;
case "grades":
Recorder.gradeVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], tstResult[x, 6], tstResult[x, 7], tstResult[x, 8], pth);
break;
case "image":
Recorder.imgVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg);
break;
case "link":
Recorder.lnkVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg);
break;
case "table":
Recorder.tblVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], pth, getNeg);
break;
case "text":
case "field text":
Recorder.txtVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg);
break;
case "outcome":
Recorder.outcomeVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], tstResult[x, 6], tstResult[x, 7], tstResult[x, 8], pth);
break;
case "percentage":
Recorder.pctVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], pth);
break;
case "weight":
Recorder.weightWrite(tstResult[x, 2], tstResult[x, 3], pth);
break;
}
else
{
TextFileOps.Write(pth, tstResult[x, 1].ToString(), Convert.ToInt32(tstResult[x, 2]));
}
}
else
{
TextFileOps.Write(pth, "<div>", 100);
TextFileOps.Write(pth, "Exception Found.............(" + step + ")", 80);
TextFileOps.Write(pth, tstResult[x, 1].ToString(), -1);
}
TextFileOps.Write(pth, "</div>", 100);
}
vfyFunc = 0;
tstResult = null;
TextFileOps.Write(pth, "<br />", 100);
//call driveFunction to execute the step noted in dataArray[tstSuite. 0]
TextFileOps.Write(pth, "</li>", 100);
if (tstFail == -1)
fnlFail = -1;
if (fndExcep == -1)
{
break;
}
}
break;
}
}
TextFileOps.Write(pth, "</ul>", 100);
switch (radnum)
{
case 1:
{
TextFileOps.Write(pth, "<li>", 80);
TextFileOps.Write(pth, "Closing Chrome..........\r\n", 80);
TextFileOps.Write(pth, "</li>", 80);
break;
}
case 2:
TextFileOps.Write(pth, "<li>", 80);
TextFileOps.Write(pth, "Closing Internet Explorer..........\r\n", 80);
TextFileOps.Write(pth, "</li>", 80);
break;
{
}
default:
//close Firefox
TextFileOps.Write(pth, "<li>", 80);
TextFileOps.Write(pth, "Closing Firefox..........\r\n", 80);
TextFileOps.Write(pth, "</li>", 80);
break;
}
//exit chromedriver.exe if Chrome is the browser being used if niot here by an exception
if (fndExcep != -1)
{
if (radnum == 1 || radnum == 2)
tstObj.Quit();
else
tstObj.Close();
}
//put a couple newlines in the result file to seperate results
for (int a = 0; a < 1; a++)
TextFileOps.Write(pth, "\r\n", 0);
}
private void imagePanel_DragDrop(object sender, DragEventArgs e)
{
if (!validData) return;
string medrecnum = "";
MemoryStream ms = e.Data.GetData("Synapse.FujiOffset") as MemoryStream;
StreamReader sr;
if (ms != null)
{
sr = new StreamReader(ms, Encoding.Unicode);
medrecnum = sr.ReadToEnd().TrimEnd('\0');
}
ms = e.Data.GetData("UniformResourceLocator") as MemoryStream;
if (ms == null) // Not a Synapse drag event
{
Array data = e.Data.GetData("FileDrop") as Array;
if ((data != null) && (data.GetValue(0) is String))
{
string dcmfile = ((string[])data)[0];
if (File.Exists(dcmfile))
{
getDemographics(dcmfile, medrecnum, true, false);
return;
}
}
}
if (cbNetwork.Checked)
{
ms = e.Data.GetData("UniformResourceLocator") as MemoryStream;
if (ms != null)
{
sr = new StreamReader(ms);
string rawstring = sr.ReadToEnd().TrimEnd('\0');
//textBoxLine(rawstring);
int epath_loc = rawstring.IndexOf("epath=");
if (epath_loc != -1)
{
string epath = DecodeFrom64(rawstring.Substring(epath_loc + 6));
epath = epath.Substring(0, epath.LastIndexOf("&") + 1);
epath = epath.Replace("&", "%26");
epath = epath.Replace("%3A", "%253A");
epath = epath.Replace("%2F", "%252F");
rawstring = rawstring.Substring(0, epath_loc) + "path=" + epath;
}
textLoc.Text = mapToLocation(rawstring);
string datasource = Regex.Match(rawstring, @"datasource=(.*?)%26").Groups[1].Value;
datasource = datasource.Replace("%253A", ":");
datasource = datasource.Replace("%252F", "/");
Uri uriImageURL = new Uri(datasource);
string studyUID = Regex.Match(rawstring, @"studyuid=(\d*)").Groups[1].Value;
string imageUID = Regex.Match(rawstring, @"imageuid=(\d*)").Groups[1].Value;
string uriBase = uriImageURL.GetLeftPart(UriPartial.Authority);
Uri uriFujiRDS = new Uri(uriBase + "/SynapseScripts/fujirds.asp");
string querystr;
querystr = @"select p.external_eid as mrn,
s.ris_study_euid as accessionnumber,
description,
study_timedate
from patient p,
procedure_info pi,
study s,
image i
where pi.id=s.procedure_info_uid and p.id=
s.patient_uid and s.id=i.study_uid and i.id=" + imageUID;
byte[] result = retrieveRDS(uriFujiRDS, querystr);
if (result == null) return;
ADODB.Recordset rs = new ADODB.Recordset();
string tempfile = Path.GetTempFileName();
ByteArrayToFile(tempfile, result);
rs.Open(tempfile, "Provider=MSPersist", ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 0);
string description = rs.Fields["description"].Value.ToString();
DateTime dt = DateTime.Parse(rs.Fields["study_timedate"].Value.ToString());
string mrn = rs.Fields["mrn"].Value.ToString();
rs.Close();
File.Delete(tempfile);
textDesc.Text = "";
textStudy.Text = description;
textMRN.Text = mrn;
textDate.Text = dt.ToString("yyyy-MM-dd");
}
}
else
{
string studyUID = "";
ms = e.Data.GetData("Synapse.TC") as MemoryStream;
if (ms != null)
{
sr = new StreamReader(ms, Encoding.Unicode);
string[] tcString = sr.ReadToEnd().Trim('\0').Split(',');
foreach (string s in tcString)
{
if (s.StartsWith("S="))
studyUID = s.Substring(2);
}
}
string imageURL = "";
ms = e.Data.GetData("UniformResourceLocator") as MemoryStream;
if (ms != null)
{
sr = new StreamReader(ms);
imageURL = sr.ReadToEnd().TrimEnd('\0');
int epath_loc = imageURL.IndexOf("epath=");
if (epath_loc != -1)
{
string epath = DecodeFrom64(imageURL.Substring(epath_loc + 6));
epath = epath.Substring(0, epath.LastIndexOf("&") + 1);
epath = epath.Replace("&", "%26");
epath = epath.Replace("%3A", "%253A");
epath = epath.Replace("%2F", "%252F");
imageURL = imageURL.Substring(0, epath_loc) + "path=" + epath;
}
textLoc.Text = mapToLocation(imageURL);
}
ArrayList results = WebCacheTool.WinInetAPI.FindUrlCacheEntries(@"/\d\.\d.*\)$");
ArrayList prefixes = new ArrayList();
SortedList<DateTime, String> slist = new SortedList<DateTime, String>();
foreach (WebCacheTool.WinInetAPI.INTERNET_CACHE_ENTRY_INFO entry in results)
{
DateTime dt = WebCacheTool.Win32API.FromFileTime(entry.LastAccessTime);
if ((DateTime.Now - dt) > TimeSpan.FromHours(24)) continue;
string fname = entry.lpszLocalFileName;
int index = fname.IndexOf('(');
if (index > 0)
{
string prefix = fname.Substring(0, index);
if (prefixes.Contains(prefix) || slist.ContainsKey(dt)) continue;
prefixes.Add(prefix);
slist.Add(dt, fname);
}
}
byte[] readBuffer = new byte[65536];
int bytesRead;
byte[] mrn = { 0x10, 0x00, 0x20, 0x00, 0x4c, 0x4f };
byte[] sid = { 0x20, 0x00, 0x0d, 0x00, 0x55, 0x49 };
string test_sid = "not found";
string dicom_mrn = "";
string fname_match = "";
if (studyUID!="")
{
for (int i = slist.Count - 1; i >= 0; i--)
{
fname_match = slist.Values[i];
using (Stream s = new FileStream(fname_match, FileMode.Open, FileAccess.Read))
{
bytesRead = s.Read(readBuffer, 0, readBuffer.Length);
test_sid = getDicomString(readBuffer, sid, bytesRead);
if (test_sid == studyUID) break;
}
}
if (test_sid != studyUID)
{
fname_match = "";
}
}
if ((fname_match=="") && (medrecnum!=""))
{
for (int i = slist.Count - 1; i >= 0; i--)
{
fname_match = slist.Values[i];
using (Stream s = new FileStream(fname_match, FileMode.Open, FileAccess.Read))
{
bytesRead = s.Read(readBuffer, 0, readBuffer.Length);
dicom_mrn = getDicomString(readBuffer, mrn, bytesRead);
if (dicom_mrn == medrecnum) break;
}
}
if (dicom_mrn != medrecnum)
{
return;
}
}
if (fname_match!="") getDemographics(fname_match, medrecnum, test_sid==studyUID, true);
}
}
protected void UpdateEntity(string Entity, string ID, string Field, string Value)
{
object oMissing = System.Reflection.Missing.Value;
//get the DataService to get a connection string to the database
Sage.Platform.Data.IDataService datasvc = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Data.IDataService>();
ADODB.Connection objConn = new ADODB.Connection();
ADODB.Recordset objRS = new ADODB.Recordset();
string strSQL = "SELECT " + Field + " FROM " + Entity + " WHERE " +Entity + "ID = '" + ID + "'";
try
{
objConn.Open(datasvc.GetConnectionString(),null, null,0 );
objRS.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
objRS.CursorType = ADODB.CursorTypeEnum.adOpenDynamic;
objRS.LockType = ADODB.LockTypeEnum.adLockOptimistic;
objRS.Open(strSQL, objConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic,-1);
if (!objRS.EOF)
{
//updating
try
{
objRS.Fields[Field].Value = Value;
}
catch (Exception ex)
{
}
}
objRS.Update(oMissing ,oMissing );
objRS.Close();
}
catch (Exception ex)
{
}
}
public static ADODB.Recordset getRSreport1(ref object sql)
{
ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
// ERROR: Not supported in C#: OnErrorStatement
functionReturnValue = new ADODB.Recordset();
functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
functionReturnValue.Open(sql, cnnDBConsReport, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
return functionReturnValue;
getRSreport1_Error:
if (cnnDBConsReport == null) {
Interaction.MsgBox(Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " cnnDBConsReport object has not been made.");
} else {
Interaction.MsgBox("Error while getRSreport1 and Error is :" + Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " --- " + cnnDBConsReport.ConnectionString + Constants.vbCrLf + Constants.vbCrLf + " --- " + cnnDBConsReport.State);
}
// ERROR: Not supported in C#: ResumeStatement
return functionReturnValue;
}
public string getAddress()
{
try
{
Position PoiX = new Position();
PoiX.X = Lat; PoiX.Y = Lon;
double tempLen = 1000000;
string tempTown = "";
ADODB.Recordset RST = new ADODB.Recordset();
string sqlSTR = "SELECT name,the_geom FROM mergedpoints WHERE the_geom && 'BOX3D(" +
(Lon - 0.5) + " " + (Lat - 0.5) + "," + (Lon + 0.5) + " " + (Lat + 0.5) +
") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat +
")', -1 ) ) < 0.5";
string sqlSTROther = "SELECT name,the_geom FROM mergedpoints WHERE the_geom && 'BOX3D(" +
(Lon - 6) + " " + (Lat - 6) + "," + (Lon + 6) + " " + (Lat + 6) +
") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat +
")', -1 ) ) < 7";
RST.Open(sqlSTR, this.odbcDatabaseConnection, ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
if (RST.EOF == true)
{
try { RST.Close(); }
catch { }
RST.Open(sqlSTROther, this.odbcDatabaseConnection, ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
}
if (RST.EOF == false)
{
RST.MoveFirst();
while (RST.EOF == false)
{
string Coord = RST.Fields["the_geom"].Value.ToString();
int Len = Coord.Length;
Coord = Right(Coord, (Len - 14));
Len = Coord.Length;
Coord = Mid(Coord, 0, (Len - 1));
char[] SepChar = { ' ' };
Array coordArray = Coord.Split(SepChar);
double xlon = Convert.ToDouble(coordArray.GetValue(0).ToString());
double xlat = Convert.ToDouble(coordArray.GetValue(1).ToString());
Position PoiY = new Position();
PoiY.X = xlat; PoiY.Y = xlon;
Calculations calc = new Calculations();
double xLen = calc.CalculateDistace(PoiX, PoiY);
//MessageBox.Show(xLen.ToString());
if (xLen < tempLen)
{
tempLen = xLen;
tempTown = RST.Fields["name"].Value.ToString();
}
//PoiY = null;
coordArray = null;
RST.MoveNext();
}
RST.Close();
RST = null;
}
if (tempLen != 1000000)
{
string retVal = Decimal.Round((decimal)tempLen, 3).ToString();
return retVal + "Km From " + tempTown;
}
else
{
return " ";
}
}
catch (System.Exception qw) { return " "; }
}
private void loadCompanies()
{
ADODB.Recordset rs = new ADODB.Recordset();
System.Windows.Forms.ListViewItem lListitem = null;
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
//If openConnection Then
//End If
rs.Open("SELECT locationCompany.locationCompanyID, location.location_Name, locationCompany.locationCompany_Name, locationCompany.locationCompany_Path FROM location INNER JOIN locationCompany ON location.locationID = locationCompany.locationCompany_LocationID ORDER BY location.location_Name, locationCompany.locationCompany_Name;", cnnDBmaster, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
//If lvLocation.Items.Count <> 0 Then
//Me.lvLocation.Items.Clear()
//End If
if (rs.RecordCount) {
while (!(rs.EOF)) {
lListitem = lvLocation.Items.Add("k" + rs.Fields("locationCompanyID").Value, rs.Fields("locationCompany_Name").Value, 2);
if (lListitem.SubItems.Count > 0) {
lListitem.SubItems[0].Text = rs.Fields("location_Name").Value + "";
} else {
lListitem.SubItems.Insert(0, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("location_Name").Value + ""));
}
if (lListitem.SubItems.Count > 1) {
lListitem.SubItems[1].Text = rs.Fields("locationCompany_Path").Value + "";
} else {
lListitem.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("locationCompany_Path").Value + ""));
}
if (Strings.LCase(rs.Fields("locationCompany_Path").Value + "") == Strings.LCase(modRecordSet.serverPath + "pricing.mdb")) {
lListitem.Selected = true;
lvLocation_DoubleClick(lvLocation, new System.EventArgs());
}
rs.MoveNext();
}
}
}
public string getRoadName()
{
try
{
Position PoiX = new Position();
PoiX.X = Lat; PoiX.Y = Lon;
double tempLen = 1000000;
string tempTown = "";
ADODB.Recordset RST = new ADODB.Recordset();
/* string sqlSTR = "SELECT name,the_geom FROM " + this.roadsTableName +" WHERE the_geom && 'BOX3D(" +
(Lon - 0.1) + " " + (Lat - 0.1) + "," + (Lon + 0.1) + " " + (Lat + 0.1) +
") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat +
")', -1 ) ) < 0.11";
string sqlSTROther = "SELECT name,the_geom FROM " + this.roadsTableName + " WHERE the_geom && 'BOX3D(" +
(Lon - 1) + " " + (Lat - 1) + "," + (Lon + 1) + " " + (Lat + 1) +
") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat +
")', -1 ) ) < 1.1";*/
string sqlSTR = "SELECT rd_name,the_geom FROM kRoads WHERE the_geom && 'BOX3D(" +
(Lon - 0.1) + " " + (Lat - 0.1) + "," + (Lon + 0.1) + " " + (Lat + 0.1) +
") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat +
")', -1 ) ) < 0.11";
string sqlSTROther = "SELECT rd_name,the_geom FROM kRoads WHERE the_geom && 'BOX3D(" +
(Lon - 1) + " " + (Lat - 1) + "," + (Lon + 1) + " " + (Lat + 1) +
") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat +
")', -1 ) ) < 1.1";
RST.Open(sqlSTR, this.odbcDatabaseConnection, ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
if (RST.EOF == true)
{
try { RST.Close(); }
catch { }
RST.Open(sqlSTROther, this.odbcDatabaseConnection, ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
}
if (RST.EOF == false)
{
RST.MoveFirst();
while (RST.EOF == false)
{
/*we are no longer dealing with a single point we are
* dealind with a line string*/
string Coord = RST.Fields["the_geom"].Value.ToString();
int Len = Coord.Length;
Coord = Right(Coord, (Len - 25));
Len = Coord.Length;
Coord = Mid(Coord, 0, (Len - 2));
char[] SepChar = { ',' };
Array pointArray = Coord.Split(SepChar);
/*lets loop through the line string*/
for (int p = 0; p < pointArray.Length; p++)
{
try
{
char[] pointSep = { ' ' };
Array coordArray = pointArray.GetValue(p).ToString().Split(pointSep);
double xlon = Convert.ToDouble(coordArray.GetValue(0).ToString());
double xlat = Convert.ToDouble(coordArray.GetValue(1).ToString());
Position PoiY = new Position();
PoiY.X = xlat; PoiY.Y = xlon;
Calculations calc = new Calculations();
double xLen = 10000001;
try { xLen = calc.CalculateDistace(PoiX, PoiY); }
catch { }
calc = null;
//MessageBox.Show(xLen.ToString());
if (xLen < tempLen)
{
tempLen = xLen;
if (tempLen > 0.5)
{ tempTown = " Along Unknown Road"; }
else
{ tempTown = " Along " + RST.Fields["rd_name"].Value.ToString(); }
}
//PoiY = null;
coordArray = null;
//Application.DoEvents();
}
catch { }
}
//Application.DoEvents();
RST.MoveNext();
}
RST.Close();
RST = null;
}
if (tempLen != 1000000)
{
return tempTown;
}
else
{
return " ";
}
}
catch (System.Exception qw) { return " "; }
}
private void cmdInv_Click(System.Object eventSender, System.EventArgs eventArgs)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
int personID = 0;
int posID = 0;
string[] lArray = null;
string lAddress = null;
//On Error Resume Next
ADODB.Connection cn = default(ADODB.Connection);
short x = 0;
string databaseName = null;
int lID = 0;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsItems = default(ADODB.Recordset);
string sql = null;
//UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"'
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
if (lvTransaction.FocusedItem == null)
return;
//lID = Mid(Split(Me.lvTransaction.SelectedItem.Key, "_")(0), 2)
if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[2]) == 0) {
Interaction.MsgBox("There is no Sale document attached!", MsgBoxStyle.Information);
System.Windows.Forms.Cursor.Current = Cursors.Default;
return;
}
lID = Convert.ToInt32(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[2]);
//If openConnection() Then
// frmMain.lblPath.Caption = serverPath
// closeConnection
//End If
//Set cn = openConnectionInstance()
//lMonth = cmbMonth.ItemData(cmbMonth.ListIndex)
//Dim lLineitem As lineitem
//If lMonth = gMonthEnd Then
// databaseName = ""
//Else
// databaseName = "Month" & lMonth & ".mdb"
//End If
databaseName = Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[1];
cn = modRecordSet.openConnectionInstance(ref databaseName);
if (cn == null) {
return;
}
transaction lTransaction = new transaction();
lineItem lLineitem = null;
customer lCustomer = new customer();
transactionSpecial lSpecial = new transactionSpecial();
if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[3]) == 2) {
//sale
//Dim Report As New cryReceipt
//Set Report = New cryReceipt
rs = new ADODB.Recordset();
sql = "SELECT Sale.* From Sale WHERE (((SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
lTransaction.cashierID = rs.Fields("Sale_PersonID").Value;
lTransaction.channelID = rs.Fields("Sale_ChannelID").Value;
lTransaction.managerID = rs.Fields("Sale_ManagerID").Value;
lTransaction.paymentDiscount = rs.Fields("Sale_Discount").Value;
lTransaction.paymentSlip = rs.Fields("Sale_Slip").Value;
lTransaction.paymentSubTotal = rs.Fields("Sale_SubTotal").Value;
lTransaction.paymentTender = rs.Fields("Sale_Tender").Value;
lTransaction.paymentTotal = rs.Fields("Sale_Total").Value;
lTransaction.paymentType = rs.Fields("Sale_PaymentType").Value;
lTransaction.posID = rs.Fields("Sale_POSID").Value;
lTransaction.transactionDate = rs.Fields("Sale_DatePOS").Value;
lTransaction.transactionID = rs.Fields("Sale_Reference").Value + "";
lTransaction.transactionType = "Sale";
//If prPrevSerial_p = True Then strSerial = rs("Sale_Serialref")
rs.Close();
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
lTransaction.companyName = rs.Fields("Company_Name").Value;
//gParameters.companyName
lTransaction.footer = " ";
//gParameters.footer & ""
lTransaction.heading1 = rs.Fields("Company_PhysicalAddress").Value;
//gParameters.heading1 & ""
lTransaction.heading2 = " ";
//gParameters.heading2 & ""
lTransaction.heading3 = " ";
//gParameters.heading3 & ""
lTransaction.taxNumber = rs.Fields("Company_TaxNumber").Value;
//gParameters.taxNumber & ""
rs.Close();
sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + lTransaction.cashierID + "));";
rs = modRecordSet.getRS(ref sql);
//rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
if (rs.RecordCount) {
lTransaction.cashierName = rs.Fields("personName").Value + "";
}
rs.Close();
sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + lTransaction.managerID + "));";
rs = modRecordSet.getRS(ref sql);
//rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
if (rs.RecordCount) {
lTransaction.managerName = rs.Fields("personName").Value + "";
}
rs.Close();
sql = "SELECT POS_Name From POS WHERE (((POS.POSID)=" + lTransaction.posID + "));";
rs = modRecordSet.getRS(ref sql);
//rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
if (rs.RecordCount) {
lTransaction.posName = rs.Fields("POS_Name").Value + "";
}
rs.Close();
//sql = "SELECT SaleItem.*, StockItem.StockItem_Name AS longName, StockItem.StockItem_ReceiptName AS receiptName, Catalogue.Catalogue_Barcode AS code,'Sale' as saleType FROM (StockItem INNER JOIN SaleItem ON StockItem.StockItemID = SaleItem.SaleItem_StockItemID) INNER JOIN Catalogue ON (Catalogue.Catalogue_Quantity = SaleItem.SaleItem_ShrinkQuantity) AND (SaleItem.SaleItem_StockItemID = Catalogue.Catalogue_StockItemID) WHERE (((SaleItem.SaleItem_DepositType)=0) AND ((SaleItem.SaleItem_SaleID)=" & lID & "))"
//sql = sql & " UNION "
//sql = sql & "SELECT SaleItem.*, Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code,'Deposit' as saleType FROM Deposit INNER JOIN SaleItem ON Deposit.DepositID = SaleItem.SaleItem_StockItemID WHERE (((SaleItem.SaleItem_DepositType)<>0) AND ((SaleItem.SaleItem_SaleID)=" & lID & "));"
sql = "SELECT SaleItem.*, 'Sale' AS saleType From SaleItem Where (((SaleItem.SaleItem_DepositType) = 0) And ((SaleItem.SaleItem_SaleID) = " + lID + "))";
sql = sql + " UNION ";
sql = sql + "SELECT SaleItem.*, 'Deposit' AS saleType From SaleItem WHERE (((SaleItem.SaleItem_DepositType)<>0) AND ((SaleItem.SaleItem_SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
while (!(rs.EOF)) {
lLineitem = new lineItem();
lLineitem.build = 0;
lLineitem.depositType = rs.Fields("SaleItem_DepositType").Value;
lLineitem.id = rs.Fields("SaleItem_StockItemID").Value;
lLineitem.lineNo = rs.Fields("SaleItem_LineNo").Value;
lLineitem.originalPrice = rs.Fields("SaleItem_PriceOriginal").Value;
lLineitem.price = rs.Fields("SaleItem_Price").Value;
lLineitem.quantity = rs.Fields("SaleItem_Quantity").Value;
lLineitem.reversal = rs.Fields("SaleItem_Reversal").Value;
lLineitem.revoke = rs.Fields("SaleItem_Revoke").Value;
lLineitem.setBuild = rs.Fields("SaleItem_SetID").Value;
lLineitem.shrink = rs.Fields("SaleItem_ShrinkQuantity").Value;
lLineitem.vat = rs.Fields("SaleItem_Vat").Value;
lLineitem.transactionType = rs.Fields("SaleType").Value + "";
//lLineitem.code = rs("Code") & ""
switch (lLineitem.depositType) {
case 0:
sql = "SELECT StockItem.StockItem_Name AS longName, StockItem.StockItem_ReceiptName AS receiptName From StockItem WHERE (((StockItemID)=" + rs.Fields("SaleItem_StockItemID").Value + "));";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.name = rs.Fields("SaleItem_ShrinkQuantity").Value + " x " + rsItems.Fields("longName").Value;
lLineitem.receiptName = rs.Fields("SaleItem_ShrinkQuantity").Value + "x" + rsItems.Fields("receiptName").Value;
}
rsItems.Close();
sql = "SELECT Catalogue.Catalogue_Barcode AS code FROM Catalogue WHERE (Catalogue.Catalogue_Quantity = " + rs.Fields("SaleItem_ShrinkQuantity").Value + ") AND (Catalogue.Catalogue_StockItemID = " + rs.Fields("SaleItem_StockItemID").Value + ");";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.code = rsItems.Fields("Code").Value + "";
}
rsItems.Close();
break;
case 1:
sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.name = rs.Fields("longName").Value + "-Unit";
lLineitem.receiptName = rs.Fields("receiptName").Value + "(U)";
lLineitem.code = rsItems.Fields("Code").Value + "";
}
rsItems.Close();
break;
case 2:
sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.name = rs.Fields("longName").Value + "-Empty Crate";
lLineitem.receiptName = rs.Fields("receiptName").Value + "(E)";
lLineitem.code = rsItems.Fields("Code").Value + "";
}
rsItems.Close();
break;
case 3:
sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.name = rs.Fields("longName").Value + "-Full Case";
lLineitem.receiptName = rs.Fields("receiptName").Value + "(F)";
lLineitem.code = rsItems.Fields("Code").Value + "";
}
rsItems.Close();
break;
}
lTransaction.lineItems.Add(ref lLineitem);
rs.MoveNext();
}
rs.Close();
sql = "SELECT Customer.*, CustomerTransaction.* FROM (CustomerTransaction INNER JOIN Sale ON CustomerTransaction.CustomerTransaction_ReferenceID = Sale.SaleID) INNER JOIN Customer ON CustomerTransaction.CustomerTransaction_CustomerID = Customer.CustomerID WHERE (((Sale.SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
lCustomer.channelID = lTransaction.channelID;
lCustomer.creditLimit = rs.Fields("Customer_ChannelID").Value;
lCustomer.department = rs.Fields("Customer_DepartmentName").Value + "";
lCustomer.fax = rs.Fields("Customer_Fax").Value + "";
lCustomer.Key = rs.Fields("CustomerID").Value;
lCustomer.name = rs.Fields("Customer_InvoiceName").Value + "";
lCustomer.outstanding = 0;
lCustomer.person = rs.Fields("Customer_FirstName").Value + " " + rs.Fields("Customer_Surname").Value;
lCustomer.physical = rs.Fields("Customer_PhysicalAddress").Value + "";
lCustomer.postal = rs.Fields("Customer_PostalAddress").Value + "";
lCustomer.signed_Renamed = rs.Fields("CustomerTransaction_PersonName").Value;
lCustomer.telephone = rs.Fields("Customer_Telephone").Value + "";
lCustomer.terms = Convert.ToInt16(rs.Fields("Customer_Terms").Value + "");
lCustomer.tax = rs.Fields("Customer_VatNumber").Value + "";
if (rs.Fields("CustomerTransaction_TransactionTypeID").Value == 3) {
lTransaction.transactionType = "Payment";
lTransaction.paymentDiscount = 0;
}
lTransaction.customer_Renamed = lCustomer;
}
rs.Close();
sql = "SELECT Consignment.* FROM Consignment INNER JOIN Sale ON Consignment.Consignment_SaleID = Sale.SaleID WHERE (((Sale.SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.BOF & rs.EOF) {
} else {
lSpecial.address = rs.Fields("Consignment_Address").Value;
lSpecial.mobile = rs.Fields("Consignment_Mobile").Value;
lSpecial.name = rs.Fields("Consignment_Name").Value;
lSpecial.quote = 0;
lSpecial.saleID = lID;
lSpecial.telephone = rs.Fields("Consignment_Number").Value;
lSpecial.transactionType = "Consignment";
lTransaction.transactionSpecial_Renamed = lSpecial;
}
rs.Close();
sql = "SELECT Consignment.* FROM Consignment INNER JOIN Sale ON Consignment.Consignment_CompleteSaleID = Sale.SaleID WHERE (((Sale.SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.BOF & rs.EOF) {
} else {
lSpecial.address = rs.Fields("Consignment_Address").Value;
lSpecial.mobile = rs.Fields("Consignment_Mobile").Value;
lSpecial.name = rs.Fields("Consignment_Name").Value;
lSpecial.quote = 0;
lSpecial.saleID = lID;
lSpecial.telephone = rs.Fields("Consignment_Number").Value;
lSpecial.transactionType = "Consignment Complete";
lTransaction.transactionSpecial_Renamed = lSpecial;
}
printTransactionA4(ref lTransaction);
}
} else if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[3]) == 3) {
//payment
//Dim Report As New cryPayment
Report.Load("cryPayment.rpt");
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
if (Information.IsDBNull(rs.Fields("Company_PhysicalAddress").Value)) {
} else {
lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", ");
if (Strings.Right(lAddress, 2) == ", ") {
lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2);
}
}
Report.SetParameterValue("txtCompanyDetails1", lAddress);
if (Information.IsDBNull(rs.Fields("Company_Telephone").Value)) {
} else {
Report.SetParameterValue("txtCompanyDetails2", rs.Fields("Company_Telephone"));
}
if (Information.IsDBNull(rs.Fields("Company_TaxNumber").Value)) {
} else {
Report.SetParameterValue("txtCompanyDetails4", "TAX NO :" + rs.Fields("Company_TaxNumber").Value);
}
//If lTransaction.heading2 <> "" Then
// Report.txtCompanyDetails2.SetText lTransaction.heading2
// If lTransaction.heading3 <> "" Then
// Report.txtCompanyDetails3.SetText lTransaction.heading3
//
// Else
// If lTransaction.taxNumber <> "" Then
// Report.txtCompanyDetails4.SetText "TAX NO :" & lTransaction.taxNumber
// End If
// End If
//Else
// If lTransaction.heading3 <> "" Then
// Report.txtCompanyDetails2.SetText lTransaction.heading3
// Else
// If lTransaction.taxNumber <> "" Then
// Report.txtCompanyDetails2.SetText "TAX NO :" & lTransaction.taxNumber
// End If
// End If
//End If
rs.Close();
Report.SetParameterValue("txtCustomer", _txtFields_2.Text);
if (!string.IsNullOrEmpty(_txtFields_6.Text)) {
lArray = Strings.Split(_txtFields_6.Text, Constants.vbCrLf);
Report.SetParameterValue("txtCustAddress1", lArray[0]);
if (Information.UBound(lArray) >= 1)
Report.SetParameterValue("txtCustAddress2", lArray[1]);
if (Information.UBound(lArray) >= 2)
Report.SetParameterValue("txtCustAddress3", lArray[2]);
if (Information.UBound(lArray) >= 3)
Report.SetParameterValue("txtCustAddress4", lArray[3]);
}
if (!string.IsNullOrEmpty(_txtFields_0.Text))
Report.SetParameterValue("txtCustAddress5", "TAX NO: " + _txtFields_0.Text);
//If LCase(databaseName) = "pricing.mdb" Then
// sql = "SELECT Sale.SaleID, POS.POS_Name, Person.Person_FirstName, Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN Person ON Sale.Sale_PersonID = Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));"
//Else
// sql = "SELECT Sale.SaleID, POS.POS_Name, M_Person.Person_FirstName, M_Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN M_Person ON Sale.Sale_PersonID = M_Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));"
//End If
//sql = "SELECT Sale.SaleID, POS.POS_Name, Person.Person_FirstName, Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN Person ON Sale.Sale_PersonID = Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));"
sql = "SELECT Sale.* From Sale WHERE (((SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
posID = rs.Fields("Sale_PosID").Value;
personID = rs.Fields("Sale_PersonID").Value;
Report.SetParameterValue("txtInvoiceNumber", rs.Fields("Sale_Reference"));
Report.SetParameterValue("txtInvoiceDate", Strings.Format(rs.Fields("Sale_DatePOS").Value, "dd mmm yyyy hh:mm"));
//Report.txtPOS.SetText rs("POS_Name")
//Report.txtCashier.SetText rs("Person_FirstName") & rs("Person_LastName")
Report.SetParameterValue("txtAmount", Strings.FormatNumber(rs.Fields("Sale_Total").Value, 2));
Report.SetParameterValue("txtChange", Strings.FormatNumber(rs.Fields("Sale_Tender").Value - rs.Fields("Sale_Total").Value, 2));
// lTransaction.paymentTotal - lTransaction.paymentTender, 2)
Report.SetParameterValue("txtTender", Strings.FormatNumber(rs.Fields("Sale_Tender").Value, 2));
// lTransaction.paymentTender, 2)
//Report.txtPaidBy.SetText lTransaction.customer.signed
Report.ReportDefinition.Sections("txtPaidBy").SectionFormat.EnableSuppress = true;
rs.Close();
sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + personID + "));";
rs = modRecordSet.getRS(ref sql);
if (rs.RecordCount) {
Report.SetParameterValue("txtCashier", rs.Fields("personName").Value + "");
}
rs.Close();
sql = "SELECT POS_Name From POS WHERE (((POS.POSID)=" + posID + "));";
rs = modRecordSet.getRS(ref sql);
if (rs.RecordCount) {
Report.SetParameterValue("txtPOS", rs.Fields("POS_Name").Value + "");
}
rs.Close();
sql = "SELECT CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_ReferenceID From CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_TransactionTypeID)=8) AND ((CustomerTransaction.CustomerTransaction_ReferenceID)=" + lID + "));";
Debug.Print(sql);
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
Report.SetParameterValue("txtSettlement", Strings.FormatNumber(rs.Fields("CustomerTransaction_Amount").Value, 2));
} else {
Report.SetParameterValue("txtSettlement", Strings.FormatNumber("0.00", 2));
}
My.MyProject.Forms.frmReportShow.Text = "Customer Statement";
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
} else {
ReportNone.Load("cryNoRecords.rpt");
ReportNone.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
ReportNone.SetParameterValue("txtTitle", "Customer Statement");
//UPGRADE_WARNING: Couldn't resolve default property of object ReportNone.txtTitle. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
}
}
private void CustomerStatement(ref int id)
{
ADODB.Recordset rsInterest = default(ADODB.Recordset);
ADODB.Recordset rsCustTransCheck = default(ADODB.Recordset);
ADODB.Recordset rsTransaction = default(ADODB.Recordset);
ADODB.Recordset rsCompany = default(ADODB.Recordset);
string lNumber = null;
string lAddress = null;
ADODB.Recordset rs = new ADODB.Recordset();
string sql = null;
//Dim Report As New cryCustomerStatement_AGING 'cryCustomerStatement
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryCustomerStatement_AGING.rpt");
System.DateTime lDate = default(System.DateTime);
rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth - (this.cmbMonthEnd.SelectedIndex - 1) + "));");
Report.SetParameterValue("txtStatementDate", Strings.Format(rs.Fields("MonthEnd_Date").Value, "dd mmm yyyy"));
lDate = rs.Fields("MonthEnd_Date").Value;
rs.Close();
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
lDate = System.Date.FromOADate(lDate.ToOADate() + 10);
lDate = System.Date.FromOADate(lDate.ToOADate() - DateAndTime.Day(lDate)) + rs.Fields("Company_PaymentDay").Value;
if (chkFields.CheckState) {
Report.SetParameterValue("txtPaymentDate", " ");
Report.SetParameterValue("Text7", " ");
} else {
Report.SetParameterValue("txtPaymentDate", Strings.Format(lDate, "dd mmm yyyy"));
}
lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", ");
if (Strings.Right(lAddress, 2) == ", ") {
lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2);
}
Report.Database.Tables(1).SetDataSource(rs);
Report.SetParameterValue("txtAddress", lAddress);
lNumber = "";
if (!string.IsNullOrEmpty(rs.Fields("Company_Telephone").Value))
lNumber = lNumber + "Tel: " + rs.Fields("Company_Telephone").Value;
if (!string.IsNullOrEmpty(rs.Fields("Company_Fax").Value)) {
if (!string.IsNullOrEmpty(lNumber))
lNumber = lNumber + " / ";
lNumber = lNumber + "Fax: " + rs.Fields("Company_Fax").Value;
}
if (!string.IsNullOrEmpty(rs.Fields("Company_Email").Value)) {
if (!string.IsNullOrEmpty(lNumber))
lNumber = lNumber + " / ";
lNumber = lNumber + "Email: " + rs.Fields("Company_Email").Value;
}
Report.SetParameterValue("txtNumbers", lNumber);
//New banking details
if (Information.IsDBNull(rs.Fields("Company_BankName").Value)) {
} else {
Report.SetParameterValue("txtBankName", rs.Fields("Company_BankName"));
}
if (Information.IsDBNull(rs.Fields("Company_BranchName").Value)) {
} else {
Report.SetParameterValue("txtBranchName", rs.Fields("Company_BranchName"));
}
if (Information.IsDBNull(rs.Fields("Company_BranchCode").Value)) {
} else {
Report.SetParameterValue("txtBranchCode", rs.Fields("Company_BranchCode"));
}
if (Information.IsDBNull(rs.Fields("Company_AccountNumber").Value)) {
} else {
Report.SetParameterValue("txtAccountNumber", rs.Fields("Company_AccountNumber"));
}
//...................
rsCompany = new ADODB.Recordset();
rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
Report.SetParameterValue("txtDaysCurr", "R " + Strings.FormatNumber(rsCompany("Customer_Current"), 2));
Report.SetParameterValue("txtDays30", "R " + Strings.FormatNumber(rsCompany("Customer_30Days"), 2));
Report.SetParameterValue("txtDays60", "R " + Strings.FormatNumber(rsCompany("Customer_60Days"), 2));
Report.SetParameterValue("txtDays90", "R " + Strings.FormatNumber(rsCompany("Customer_90Days"), 2));
Report.SetParameterValue("txtDays120", "R " + Strings.FormatNumber(rsCompany("Customer_120Days"), 2));
Report.SetParameterValue("txtDays150", "R " + Strings.FormatNumber(rsCompany("Customer_150Days"), 2));
rsCompany.Close();
rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, modRecordSet.cnnDB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
Report.Database.Tables(2).SetDataSource(rsCompany);
rsTransaction = new ADODB.Recordset();
//rsTransaction.Open "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," & _
//'" TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
//changed for OPEN ITEM
//rsTransaction.Open "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," & _
//'" TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
rsCustTransCheck = new ADODB.Recordset();
rsCustTransCheck.Open("SELECT * FROM CustomerTransaction Where CustomerTransaction_CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rsCustTransCheck.Fields.Count <= 12) {
rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit, 0, 0, 0 FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + "));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
} else {
rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]), CustomerTransaction.CustomerTransaction_Reference & IIf([CustomerTransaction.CustomerTransaction_Allocated]<>[CustomerTransaction_Amount] AND [CustomerTransaction.CustomerTransaction_Allocated]<>0,' (P)',Null), CustomerTransaction.CustomerTransaction_PersonName, TransactionType.TransactionType_Name," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))>0,([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated])),Null) AS debit," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))<0,([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated])),Null) AS credit, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + ") AND ((CustomerTransaction.CustomerTransaction_Amount-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))<>0));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
}
//Report.Database.Tables(3).SetDataSource rsTransaction, 3
if (rsTransaction.BOF | rsTransaction.EOF) {
rsTransaction = new ADODB.Recordset();
rsTransaction.Open("SELECT 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0," + " 0, 0 AS debit, 0 AS credit;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
Report.Database.Tables(3).SetDataSource(rsTransaction);
//Exit Sub
} else {
Report.Database.Tables(3).SetDataSource(rsTransaction);
}
if (rsTransaction.BOF | rsTransaction.EOF) {
return;
}
//Set rsInterest = New Recordset
//rsInterest.Open ("SELECT * FROM Interest WHERE (((CustomerID)=" & id & ")) and (Debit>0);"), cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
rsInterest = modRecordSet.getRS(ref "SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);");
//If rsInterest.BOF Or rsInterest.EOF Then
if (rsInterest.RecordCount > 0) {
Report.ReportDefinition.ReportObjects("Field20").Top = 280;
Report.ReportDefinition.ReportObjects("Field21").Top = 280;
Report.ReportDefinition.ReportObjects("Field22").Top = 280;
Report.ReportDefinition.ReportObjects("Field23").Top = 280;
Report.Database.Tables(4).SetDataSource(rsInterest);
} else {
//Set rsInterest = New Recordset
//rsInterest.Open "SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
rsInterest = modRecordSet.getRS(ref "SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;");
Report.ReportDefinition.Sections("Field20").SectionFormat.EnableSuppress = true;
Report.ReportDefinition.Sections("Field21").SectionFormat.EnableSuppress = true;
Report.ReportDefinition.Sections("Field22").SectionFormat.EnableSuppress = true;
Report.ReportDefinition.Sections("Field23").SectionFormat.EnableSuppress = true;
Report.Database.Tables(4).SetDataSource(rsInterest);
//Exit Sub
//Set rsInterest = New Recordset
//rsInterest.Open "SELECT * FROM Interest WHERE (((CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
}
if (rs.Fields("Company_DebtorPrintBal").Value == true) {
CrystalDecisions.CrystalReports.Engine.TextObject tObj = Report.ReportDefinition.ReportObjects("Text5");
tObj.Color = Color.White;
}
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
// frmReportShow.CRViewer1.PrintReport
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
// Unload frmReportShow
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
