public object[] db_access(string strSQL)
{
ADODB.Connection objCon;
ADODB.Recordset objRec;
object[,] dataRows;
object[] dataSuite;
string strCon;
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
//establish the connection string and open the database connection
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon.Open(strCon);
//execute the SQL and return the recrodset of results
objRec = objCon.Execute(strSQL, out missing, 0);
//populate a two dinmensional object array with the results
dataRows = objRec.GetRows();
//get a one dimensional array that can be placed into the Test Suite dropdown
dataSuite = thinArray(dataRows);
//close the recordset
objRec.Close();
//close the database connection
objCon.Close();
return dataSuite;
}
private void cmdClose_Click(System.Object eventSender, System.EventArgs eventArgs)
{
int MStockNew = 0;
// ERROR: Not supported in C#: OnErrorStatement
ADODB.Recordset rsB = default(ADODB.Recordset);
ADODB.Recordset rsk = default(ADODB.Recordset);
rsB = new ADODB.Recordset();
rsk = new ADODB.Recordset();
rsk = modRecordSet.getRS(ref "SELECT * FROM " + Te_Name + "");
if (rsk.RecordCount > 0)
{
//UPGRADE_WARNING: Couldn't resolve default property of object MStockNew. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
MStockNew = 2;
rsB = modRecordSet.getRS(ref "INSERT INTO StockGroup(StockGroup_Name,StockGroup_Disabled)VALUES('" + Te_Name + "',0)");
UpdateCatalogID(ref (Te_Name));
}
MyErH:
if (rsLIQ.State)
{
rsLIQ.Close();
}
this.Close();
}
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);
}
//传入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);
}
}
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 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();
}
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 bool loadData(ref string lName)
{
bool functionReturnValue = false;
ADODB.Recordset rs = default(ADODB.Recordset);
string lSQL = null;
int x = 0;
string lID = null;
gField = lName;
rs = modRecordSet.getRS(ref "SELECT * From ftConstruct WHERE (ftConstruct_Name = '" + Strings.Replace(lName, "'", "''") + "')");
gFieldID = rs.Fields("ftConstruct_FieldID").Value;
gFieldName = rs.Fields("ftConstruct_FieldName").Value;
gHeading = rs.Fields("ftConstruct_DisplayName").Value;
lSQL = rs.Fields("ftConstruct_SQL").Value;
rs.Close();
rs = modRecordSet.getRS(ref lSQL);
//Display the list of Titles in the DataCombo
loading = true;
functionReturnValue = false;
x = -1;
gArray = new string[rs.RecordCount, 3];
while (!(rs.EOF))
{
x = x + 1;
gArray[x, 0] = rs.Fields(gFieldID).Value;
gArray[x, 1] = rs.Fields(gFieldName).Value + "";
gArray[x, 2] = Convert.ToString(0);
rs.MoveNext();
}
rs.Close();
rs = modRecordSet.getRS(ref "SELECT ftDataItem_ID From ftDataItem WHERE (ftDataItem_PersonID = " + modRecordSet.gPersonID + ") AND (ftDataItem_FieldName = '" + Strings.Replace(gField, "'", "''") + "')");
while (!(rs.EOF))
{
lID = rs.Fields("ftDataItem_ID").Value;
for (x = 0; x <= Information.UBound(gArray); x++)
{
if (lID == gArray[x, 0])
{
gArray[x, 2] = Convert.ToString(1);
}
}
rs.MoveNext();
}
rs.Close();
setup();
functionReturnValue = true;
loadLanguage();
ShowDialog();
loading = false;
return(functionReturnValue);
}
public static DataTable LoadDBF(string FilePath, string Sql)
{
ADODB.Recordset rs = new ADODB.Recordset();
ADODB.Connection conn = new ADODB.Connection();
try
{
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='dbase 5.0';Data Source = " + System.IO.Path.GetDirectoryName(FilePath));
rs.Open(Sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly);
object ary = rs.GetRows();
object[,] aa = (object[, ])ary;
int UB1 = aa.GetUpperBound(1);
int UB0 = aa.GetUpperBound(0);
DataTable dt = new DataTable();
for (int i = 0; i <= UB0; i++)
{
dt.Columns.Add(i.ToString());
}
for (int i = 0; i <= UB1; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j <= UB0; j++)
{
dr[j] = aa[j, i].ToString();
}
dt.Rows.Add(dr);
}
rs.Close();
conn.Close();
return(dt);
}
catch (Exception e)
{
rs.Close();
conn.Close();
return(null);
}
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
string sql = null;
ADODB.Recordset rsData = default(ADODB.Recordset);
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
CrystalDecisions.CrystalReports.Engine.ReportDocument Report1 = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("cryNegativeProfit.rpt");
Report1.Load("crysqZero.rpt");
string lOrder = null;
string lWhere = null;
if (gRS.RecordCount)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
//Report.VerifyOnEveryPrint = True
Report.Database.Tables(1).SetDataSource(gRS);
//Report.Database.SetDataSource(gRS, 3)
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 (gRSsq.RecordCount)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report1.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
//Report1.VerifyOnEveryPrint = True
Report1.Database.Tables(1).SetDataSource(gRSsq);
//Report1.Database.SetDataSource(gRSsq, 3)
My.MyProject.Forms.frmReportShow.Text = Report1.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report1;
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();
}
}
public static object[,] LoadExcelAry(string FilePath, string Sql)
{
ADODB.Connection conn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
try
{
//FilePath = "C:\\Users\\Administrator\\Desktop\\测试模版\\Shp\\1\\凤凰观村2组.xls";
//Sql = "Select 宗地编码,土地类型,承包方名称,承包方编码 From [地块信息$] Order By 承包方编码,宗地编码";
//Select 宗地编码,土地类型,承包方名称,承包方编码 From [地块信息$] Order By 承包方编码,宗地编码
//Select * From [发包方信息$A1:BB2000]
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();
int UB1 = aa.GetUpperBound(1);
int UB0 = aa.GetUpperBound(0);
for (int i = 0; i <= UB1; i++)
{
for (int j = 0; j <= UB0; j++)
{
ErrorMsg = (i + "-" + j);
if (Convert.IsDBNull(aa[j, i]) || aa[j, i] == null)
{
aa[j, i] = "";
}
}
}
//object test0 = aa[1, 3];
//object test1 = aa[15, 3];
//object test2 = aa[17, 3];
return(aa);
}
catch (Exception)
{
rs.Close();
conn.Close();
return(null);
}
}
public object[] db_access(string strSQL)
{
ADODB.Connection objCon;
ADODB.Recordset objRec;
object[,] dataRows;
object[] dataSuite;
string strCon;
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
//establish the connection string and open the database connection
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon.Open(strCon);
//execute the SQL and return the recrodset of results
objRec = objCon.Execute(strSQL, out missing, 0);
//populate a two dinmensional object array with the results
dataRows = objRec.GetRows();
//get a one dimensional array that can be placed into the Test Suite dropdown
dataSuite = thinArray(dataRows);
//close the recordset
objRec.Close();
//close the database connection
objCon.Close();
return(dataSuite);
}
//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);
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
string sql = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("cryStockItemPrice.rpt");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
Report.SetParameterValue("txtFilter", lblHeading.Text);
Report.SetParameterValue("txtTitle1", "Where Sales Channel = " + cmbChannel.CtlText);
//Report.Database.SetDataSource(adoPrimaryRS, 3)
Report.Database.Tables(1).SetDataSource(adoPrimaryRS);
//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();
}
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 bool IsCorrectionJob()
{
bool bReturn = false;
ADODB.Recordset oRS = null;
try
{
oRS = DataContext.OpenRecordset("select G3E_JOBTYPE from g3e_job where g3e_identifier = ?", ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, (int)ADODB.CommandTypeEnum.adCmdText, DataContext.ActiveJob);
oRS.MoveFirst();
if (Convert.ToString(oRS.Fields["G3E_JOBTYPE"].Value).Equals("NON-WR"))
{
bReturn = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oRS != null)
{
if (oRS.State == 1)
{
oRS.Close();
oRS.ActiveConnection = null;
}
oRS = null;
}
}
return(bReturn);
}
public void buildCriteria(ref string filter_Renamed)
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset RSitem = default(ADODB.Recordset);
gHeading = "";
gCriteria = "";
rs = modRecordSet.getRS(ref "SELECT * From ftOrderSet Where (((ftSet_Group) = 'order')) ORDER BY ftSet_Order;");
if (rs.BOF | rs.EOF)
{
}
else
{
while (!(rs.EOF))
{
RSitem = modRecordSet.getRS(ref "SELECT * From ftOrder WHERE (((ftData_PersonID)=" + modRecordSet.gPersonID + ") AND ((ftData_FieldName)='" + Strings.Replace(rs.Fields("ftSet_Name").Value, "'", "''") + "'));");
if (RSitem.BOF | RSitem.EOF)
{
}
else
{
gHeading = gHeading + " AND " + RSitem.Fields("ftData_Heading").Value;
gCriteria = gCriteria + " AND " + Strings.Replace(RSitem.Fields("ftData_SQL").Value, "[field]", rs.Fields("ftSet_Field").Value);
}
RSitem.Close();
rs.moveNext();
}
gCriteria = Strings.Replace(gCriteria, "=", "<>");
gCriteria = Strings.Replace(gCriteria, " OR ", " AND ");
}
rs.Close();
if (!string.IsNullOrEmpty(gHeading))
{
gHeading = Strings.Mid(gHeading, 6);
}
}
/// <summary>
/// Extracts the data contained in the ADO recordset and stores it in
/// XmlSource. If there is another recordset, then the first was actually
/// Extended attributes metadata, so that document is move to XmlExtendedAttributes,
/// and the data in the second recordset is store into XmlSource.
/// </summary>
private void GetSource()
{
ADODB.Connection oConn = null;
if (oRs == null)
{
OpenRecordset(CreateCommand(ref oConn));
}
MSXML2.DOMDocument D1 = new MSXML2.DOMDocumentClass();
// load the 1st dataset
oRs.Save(D1, ADODB.PersistFormatEnum.adPersistXML);
XmlSource = new XmlDocument();
XmlSource.LoadXml(D1.xml);
object RecordsAffected;
oRs = oRs.NextRecordset(out RecordsAffected);
if (oRs != null && oRs.State == 1)
{
XmlExtendedAttributes = XmlSource;
// Load the 2nd dataset
oRs.Save(D1, ADODB.PersistFormatEnum.adPersistXML);
XmlSource = new XmlDocument();
XmlSource.LoadXml(D1.xml);
}
if (oRs != null && oRs.State == 1)
{
oRs.Close();
}
if (oConn != null)
{
oConn.Close();
}
}
public static void AddData <T>(this IDatabaseObject <T> source) where T : class
{
string name = typeof(T).Name;
string sql = "[" + name + "]";
ADODB.Recordset rs = DataSeverConnection.Instance.Recordset(sql, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic);
rs.AddNew();
PropertyInfo[] properties = typeof(T).GetProperties();
for (int i = 0; i < rs.Fields.Count; i++)
{
PropertyInfo property = null;
properties.Each(f =>
{
if (f.Name == rs.Fields[i].Name)
{
property = f;
return;
}
});
rs.Fields[i].Value = property.GetValue(source);
}
rs.Update();
rs.Close();
}
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]);
}
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 static bool DeleteAsketPor()
{
string Select = "Select Time.Id, IdRazrabAsket from " + DB.TABLE_REDMINE_ISSUES + " as ISSUES " +
" left join " + DB.TABLE_REDMINE_TIME_ENTRIES + " as Time on ISSUES.Id = Time.IssueId " +
" where Deleted = 1 and DeletedInAsketPor = 0";
ADODB.Recordset RsPor = DB.Select(Select);
if (RsPor == null)
{
return(false);
}
string Update = "";
while (!(RsPor.EOF))
{
if (RsPor.Fields["IdRazrabAsket"].Value > 0)
{
Update = "Delete from " + DB.TABLE_REPORT_DEVELOPER + " where Код=" + RsPor.Fields["IdRazrabAsket"].Value;
DB.ExecuteQuery(Update);
Update = "Update" + DB.TABLE_REDMINE_TIME_ENTRIES + " set DeletedInAsketPor= 1 where id=" + RsPor.Fields["Id"].Value;
DB.ExecuteQuery(Update);
}
RsPor.MoveNext();
}
RsPor.Close();
return(true);
}
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);
}
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();
}
public string get_next_service_id()
{
string latest_serv_id = "";
string next_serv_id = "";
string sql = sql_lib.get_latest_servise_id();
ADODB.Recordset rs = null;
try
{
if (mdb.RsOpen(out rs, sql) == false)
{
return(next_serv_id);
}
if (rs.EOF == false)
{
latest_serv_id = rs.Fields["id"].Value.ToString();
next_serv_id = (Convert.ToInt32(latest_serv_id) + 1).ToString();
}
rs.Close();
rs = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(next_serv_id);
}
public string GetResMessage()
{
ADODB.Recordset reccust = null;
string resMessage = "";
try {
classE1_1.Open();
DbUse.open_ado_rec(classE1_1.globaldb, ref reccust, "zstblresults");
resMessage = "";
if (!reccust.EOF)
{
resMessage = resMessage + (string)reccust.Fields["Results"].Value;
reccust.MoveNext();
}
if (reccust != null)
{
reccust.Close();
reccust = null;
}
classE1_1.Close();
} catch (Exception ex) {
logFiles.ErrorLog(ex);
}
resMessage = resMessage.Replace("\n", "<br/>");
return(DbUse.reproduceRedColor(resMessage));
}
private void loadStockSales()
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsStock = default(ADODB.Recordset);
ADODB.Recordset rsGroup = default(ADODB.Recordset);
string sql = null;
ADODB.Recordset rsData = default(ADODB.Recordset);
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("crySalesByShrink.rpt");
ReportNone.Load("cryNoRecords.rpt");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modReport.getRSreport(ref "SELECT Report.Report_Heading, aCompany.Company_Name FROM aCompany, Report;");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
Report.SetParameterValue("txtDayEnd", rs.Fields("Report_Heading"));
rs.Close();
rs = modReport.getRSreport(ref "SELECT * FROM Link Where LinkID=2 AND Link_SectionID=1");
if (string.IsNullOrEmpty(rs.Fields("Link_Name").Value))
{
Report.ReportDefinition.Sections("Section2").SectionFormat.EnableSuppress = true;
}
else
{
Report.SetParameterValue("txtFilter", Strings.Replace(rs.Fields("Link_Name").Value, "''", "'"));
}
sql = "SELECT aStockItem.StockItemID, aStockItem.StockItem_Name, thejoin.shrink, thejoin.quantity, thejoin.price, thejoin.list, thejoin.actual FROM aStockItem INNER JOIN (SELECT SaleItem.SaleItem_StockItemID AS StockItem, SaleItem.SaleItem_ShrinkQuantity AS shrink, Sum(IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity])) AS quantity, Sum(([SaleItem_Price]*[SaleItem_Quantity])) AS price, Sum((([SaleItem_ListCost]+[SaleItem_DepositCost])*IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity]))*(1+[SaleItem_Vat]/100)) AS list, Sum((([SaleItem_ActualCost]+[SaleItem_DepositCost])*IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity]))*(1+[SaleItem_Vat]/100)) AS actual From SaleItem Where (((SaleItem.SaleItem_DepositType) = 0) And ((SaleItem.SaleItem_Revoke) = 0)) GROUP BY SaleItem.SaleItem_StockItemID, SaleItem.SaleItem_ShrinkQuantity ) AS thejoin ON aStockItem.StockItemID = thejoin.StockItem ";
sql = sql + rs.Fields("Link_SQL").Value + " ORDER BY aStockItem.StockItem_Name, thejoin.shrink;";
rs = modReport.getRSreport(ref sql);
if (rs.BOF | rs.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.Tables(1).SetDataSource(rs);
//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();
}
public void GetDMSInfo(long value)
{
ADODB.Recordset rs = DMSConnection.GetInstance().Recordset("SELECT * FROM [dbo.itemloc] WHERE [itm_num]=" + value.ToString());
rs.Each(r =>
{
DCLocation temp = new DCLocation(r.Fields["location"].Value.ToString());
temp.QtyOH = (int)r.Fields["qty_on_hand"].Value;
temp.PickZone = (short)r.Fields["pic_zone"].Value;
temp.StorageFunction = (byte)r.Fields["stg_fnc_code"].Value;
if (temp.LocationType == "Forward")
{
_forward = temp;
_totOH += _forward.QtyOH;
}
else
{
if (temp.PickZone < 900)
{
_reserves.Add(temp);
_totOH += temp.QtyOH;
}
}
});
rs.Close();
rs = null;
}
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 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 static void fnKillRS(ADODB.Recordset oRS)
{
if (oRS.State == 1)
{
oRS.Close();
}
oRS = null;
}
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);
}
public void ExportToCSV(bool PrintHeader = true)
{
string ExportFilePath = null;
ADODB.Recordset rs = default(ADODB.Recordset);
int i = 0;
int TotalRecords = 0;
bool ErrorOccured = false;
short NumberOfFields = 0;
const string quote = "\"";
//Faster then Chr$(34)
string sql = null;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
cmdStart.Enabled = false;
cmdExit.Enabled = false;
txtPassword.Enabled = false;
string ptbl = null;
string t_day = null;
string t_Mon = null;
if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Day(DateAndTime.Today)))) == 1)
t_day = "0" + Strings.Trim(Convert.ToString(DateAndTime.Day(DateAndTime.Today)));
else
t_day = Convert.ToString(DateAndTime.Day(DateAndTime.Today));
if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Month(DateAndTime.Today)))) == 1)
t_Mon = "0" + Strings.Trim(Convert.ToString(DateAndTime.Month(DateAndTime.Today)));
else
t_Mon = Conversion.Str(DateAndTime.Month(DateAndTime.Today));
ExportFilePath = modRecordSet.serverPath + "4POSDebtor" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Mon) + Strings.Trim(t_day);
if (fso.FileExists(ExportFilePath + ".csv"))
fso.DeleteFile((ExportFilePath + ".csv"));
rs = modRecordSet.getRS(ref "SELECT CustomerID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Current as CurrentBalance,Customer_30Days as 30Days, Customer_60Days as 60days, Customer_90Days as 90Days, Customer_120Days as 120Days,Customer_150Days as 150Days FROM Customer");
prgBar.Maximum = rs.RecordCount;
if (rs.RecordCount > 0) {
FileSystem.FileOpen(1, ExportFilePath + ".csv", OpenMode.Output);
var _with2 = modRecordSet.getRS(ref ref "SELECT CustomerID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Current as CurrentBalance,Customer_30Days as 30Days, Customer_60Days as 60days, Customer_90Days as 90Days, Customer_120Days as 120Days,Customer_150Days as 150Days FROM Customer");
rs.MoveFirst();
NumberOfFields = rs.Fields.Count - 1;
if (PrintHeader) {
//Now add the field names
for (i = 0; i <= NumberOfFields - 1; i++) {
FileSystem.Print(1, rs.Fields(i).name + ",");
//similar to the ones below
}
FileSystem.PrintLine(1, rs.Fields(NumberOfFields).name);
}
while (!rs.EOF) {
prgBar.Value = prgBar.Value + 1;
// ERROR: Not supported in C#: OnErrorStatement
TotalRecords = TotalRecords + 1;
//If there is an emty field,
for (i = 0; i <= NumberOfFields; i++) {
//add a , to indicate it is
if ((Information.IsDBNull(rs.Fields(i).Value))) {
FileSystem.Print(1, ",");
//empty
} else {
if (i == NumberOfFields) {
FileSystem.Print(1, quote + Strings.Trim(Convert.ToString(rs.Fields(i).Value)) + quote);
} else {
FileSystem.Print(1, quote + Strings.Trim(Convert.ToString(rs.Fields(i).Value)) + quote + ",");
}
}
//Putting data under "" will not
}
//confuse the reader of the file
DoEventsEx();
//between Dhaka, Bangladesh as two
FileSystem.PrintLine(1);
//fields or as one field.
rs.moveNext();
}
FileSystem.FileClose(1);
Interaction.MsgBox("Customer details were successfully exported to : " + FilePath + "" + "4POSProd" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Mon) + Strings.Trim(t_day) + ".csv", MsgBoxStyle.OkOnly, "Customers");
// DoEvents
// DoEvents
// MsgBox "Now Zeroising...", vbOKOnly, "Customers"
// cmdStart.Enabled = False
// cmdExit.Enabled = False
// Set rsZ = getRS("SELECT CustomerID FROM Customer")
// Do While Not rsZ.EOF
// DoEvents
// cmdProcess_Click (rsZ("CustomerID"))
// DoEvents
// rsZ.moveNext
// Loop
//MsgBox "Completed", vbOKOnly, "Customers"
//cmdStart.Enabled = True
//cmdExit.Enabled = True
this.Close();
}
System.Windows.Forms.Cursor.Current = Cursors.Default;
rs.Close();
//cnnDB.Close
//Set cnnDB = Nothing
//closeConnection
}
private void cmdPrintHistory_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();
bool bReset = false;
// ERROR: Not supported in C#: OnErrorStatement
//If cmdShowHistory.Caption = "Show Full History " Then
// cmdShowHistory.Caption = "Show Current Month"
//ElseIf cmdShowHistory.Caption = "Show Full History" Then
// cmdShowHistory.Caption = "Show Current Month"
//Else
// cmdShowHistory.Caption = "Show Full History "
// cmdsearch_Click
// Exit Sub
//End If
if (gLoading)
return;
gLoading = true;
Cursor = System.Windows.Forms.Cursors.WaitCursor;
System.Windows.Forms.Application.DoEvents();
y = cmbMonth.Items.Count - 1;
//lvTransaction.ListItems.Clear
//lblcount.Caption = "0 of 0"
//lvTransaction.Visible = False
sql = "DELETE tempCustomerHistory.* FROM tempCustomerHistory;";
modRecordSet.cnnDB.Execute(sql);
string lPosString = null;
//(cmbMonth.ListCount - 1)
for (i = 0; i <= y; i++) {
lMonth = Convert.ToInt32(cmbMonth.SelectedItem(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 + ") AND (CustomerTransaction.CustomerTransaction_MonthEndID=(" + lMonth + "))) ORDER BY CustomerTransaction.CustomerTransactionID DESC;";
sql = "SELECT * FROM CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + adoPrimaryRS.Fields("CustomerID").Value + ") AND (CustomerTransaction.CustomerTransaction_MonthEndID=(" + lMonth + "))) ORDER BY CustomerTransaction.CustomerTransactionID DESC;";
Debug.Print(sql);
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
//Dim lvItem As listItem
x = 0;
//lvTransaction.Visible = False
while (!(rs.EOF)) {
x = x + 1;
if (gLoading) {
} else {
break; // TODO: might not be correct. Was : Exit Do
}
bResetError:
//lblcount.Caption = x & " of " & rs.RecordCount
System.Windows.Forms.Application.DoEvents();
if (bReset) {
bReset = false;
// ERROR: Not supported in C#: OnErrorStatement
sql = "INSERT INTO tempCustomerHistory ( CustomerTransactionID, CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done ) ";
sql = sql + "SELECT " + rs.Fields("CustomerTransactionID").Value + ", " + rs.Fields("CustomerTransaction_CustomerID").Value + ", " + rs.Fields("CustomerTransaction_TransactionTypeID").Value + ", " + rs.Fields("CustomerTransaction_DayEndID").Value + ", " + rs.Fields("CustomerTransaction_MonthEndID").Value + ", " + rs.Fields("CustomerTransaction_ReferenceID").Value + ", #" + rs.Fields("CustomerTransaction_Date").Value + "#, '" + rs.Fields("CustomerTransaction_Description").Value + "', " + rs.Fields("CustomerTransaction_Amount").Value + ", '" + rs.Fields("CustomerTransaction_Reference").Value + "', '" + rs.Fields("CustomerTransaction_PersonName").Value + "', " + rs.Fields("CustomerTransaction_Done").Value + ";";
modRecordSet.cnnDB.Execute(sql);
} else {
sql = "INSERT INTO tempCustomerHistory ( CustomerTransactionID, CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done, CustomerTransaction_Main, CustomerTransaction_Child, CustomerTransaction_Allocated ) ";
sql = sql + "SELECT " + rs.Fields("CustomerTransactionID").Value + ", " + rs.Fields("CustomerTransaction_CustomerID").Value + ", " + rs.Fields("CustomerTransaction_TransactionTypeID").Value + ", " + rs.Fields("CustomerTransaction_DayEndID").Value + ", " + rs.Fields("CustomerTransaction_MonthEndID").Value + ", " + rs.Fields("CustomerTransaction_ReferenceID").Value + ", #" + rs.Fields("CustomerTransaction_Date").Value + "#, '" + rs.Fields("CustomerTransaction_Description").Value + "', " + rs.Fields("CustomerTransaction_Amount").Value + ", '" + rs.Fields("CustomerTransaction_Reference").Value + "', '" + rs.Fields("CustomerTransaction_PersonName").Value + "', " + rs.Fields("CustomerTransaction_Done").Value + ", " + rs.Fields("CustomerTransaction_Main").Value + ", " + rs.Fields("CustomerTransaction_Child").Value + ", " + rs.Fields("CustomerTransaction_Allocated").Value + ";";
modRecordSet.cnnDB.Execute(sql);
}
//If rs("CustomerTransaction_Reference") <> "Month End" Then
// Set lvItem = lvTransaction.ListItems.Add(, "K" & rs("CustomerTransactionID") & "_" & databaseName & "_" & rs("CustomerTransaction_ReferenceID") & "_" & rs("CustomerTransaction_TransactionTypeID"), Format(rs("CustomerTransaction_Date"), "yyyy mmm dd hh:mm"))
// lvItem.SubItems(1) = rs("CustomerTransaction_Reference")
// lvItem.SubItems(2) = rs("TransactionType_Name")
// lvItem.SubItems(3) = FormatNumber(rs("debit"), 2)
// lvItem.SubItems(4) = FormatNumber(rs("credit"), 2)
//End If
rs.MoveNext();
}
//lvTransaction.Visible = True
rs.Close();
nextMonth:
}
//lvTransaction.Visible = True
report_CustomerStatementFullHistory(ref adoPrimaryRS.Fields("CustomerID").Value);
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 if (Err().Number == Convert.ToDouble("3265")) {
bReset = true;
//Resume bResetError
} else {
Interaction.MsgBox(Err().Number + " - " + Err().Description);
return;
// ERROR: Not supported in C#: ResumeStatement
}
}
public void report_CustomerStatementFullHistory(ref int id)
{
ADODB.Recordset rsInterest = 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 cryCustomerStatementFull
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
System.DateTime lDate = default(System.DateTime);
short gMonth = 0;
ADODB.Connection cnnDBStatement = new ADODB.Connection();
Report.Load("cryCustomerStatementFull.rpt");
var _with1 = cnnDBStatement;
_with1.Provider = "MSDataShape";
cnnDBStatement.Open("Data Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + modRecordSet.serverPath + "pricing.mdb" + ";User Id=liquid;Password=lqd;Jet OLEDB:System Database=" + modRecordSet.serverPath + "Secured.mdw");
rs = modRecordSet.getRS(ref "SELECT Company_MonthendID FROM Company;");
if (rs.Fields("Company_MonthendID").Value <= 1) {
gMonth = 1;
} else {
gMonth = rs.Fields("Company_MonthendID").Value;
//- 1
}
rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth + "));");
//rs.Open "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" & gMonth & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
if (Information.IsDBNull(rs.Fields("MonthEnd_Date").Value) == true | rs.RecordCount == 0) {
gMonth = 1;
rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth + "));");
}
Report.SetParameterValue("txtStatementDate", Strings.Format(DateAndTime.Today, "dd mmm yyyy"));
if (rs.RecordCount) {
//Report.txtStatementDate.SetText Format(rs("MonthEnd_Date"), "dd mmm yyyy")
lDate = rs.Fields("MonthEnd_Date").Value;
} else {
}
rs.Close();
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
lDate = System.Date.FromOADate(lDate.ToOADate() + 10);
lDate = DateAndTime.DateSerial(DateAndTime.Year(lDate), DateAndTime.Month(lDate), 1);
lDate = System.Date.FromOADate(lDate + rs.Fields("Company_PaymentDay").Value - 1);
//Report.txtPaymentDate.SetText 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.Database.Tables(2).SetDataSource(rsCompany);
rsTransaction = new ADODB.Recordset();
//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
rsTransaction.Open("SELECT tempCustomerHistory.CustomerTransactionID, tempCustomerHistory.CustomerTransaction_CustomerID, tempCustomerHistory.CustomerTransaction_TransactionTypeID, tempCustomerHistory.CustomerTransaction_DayEndID, tempCustomerHistory.CustomerTransaction_MonthEndID, tempCustomerHistory.CustomerTransaction_ReferenceID, tempCustomerHistory.CustomerTransaction_Date, tempCustomerHistory.CustomerTransaction_Description, tempCustomerHistory.CustomerTransaction_Amount-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated]), tempCustomerHistory.CustomerTransaction_Reference & IIf([tempCustomerHistory.CustomerTransaction_Allocated]<>[CustomerTransaction_Amount] AND [tempCustomerHistory.CustomerTransaction_Allocated]<>0,' (P)',Null), tempCustomerHistory.CustomerTransaction_PersonName, TransactionType.TransactionType_Name," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated]))>0,([CustomerTransaction_Amount]-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated])),Null) AS debit," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated]))<0,([CustomerTransaction_Amount]-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated])),Null) AS credit, tempCustomerHistory.CustomerTransaction_Main, tempCustomerHistory.CustomerTransaction_Child, tempCustomerHistory.CustomerTransaction_Allocated FROM tempCustomerHistory INNER JOIN TransactionType ON tempCustomerHistory.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((tempCustomerHistory.CustomerTransaction_CustomerID)=" + id + ") AND ((tempCustomerHistory.CustomerTransaction_Amount-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated]))<>0)) ORDER BY tempCustomerHistory.CustomerTransaction_Date;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
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);
}
//rs.Close
rsInterest = new ADODB.Recordset();
rsInterest.Open("SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
//If rsInterest.BOF Or rsInterest.EOF Then
if (rsInterest.RecordCount > 0) {
//Report.Field20.Top = 280
//Report.Field21.Top = 280
//Report.Field22.Top = 280
//Report.Field23.Top = 280
Report.Database.Tables(4).SetDataSource(rsInterest);
} else {
rsInterest = new ADODB.Recordset();
rsInterest.Open("SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
//Report.Field20.Suppress = True
//Report.Field21.Suppress = True
//Report.Field22.Suppress = True
//Report.Field23.Suppress = 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
}
// rsInterest.Open ("SELECT Interest.*, Interest.Description,WHERE (((Interest.CustomerID)=" & id & "));"), cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
//Dim rsinte As String
// rsinte = rsInterest("Description")
//If rsInterest.RecordCount > 0 Then
// Report.Database.Tables(4).SetDataSource rsInterest, 3
//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
// Report.Database.Tables(4).SetDataSource rsInterest, 3
//End If
//Set rsTrans = New Recordset
//Dim NewDateC As String
//NewDateC = Format(Now)
//rsTrans.Open ""
//Report.Database.Tables(4).SetDataSource rsTrans, 3
if (rsTransaction.BOF | rsTransaction.EOF) {
return;
//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
}
if (rs.Fields("Company_DebtorPrintBal").Value == true) {
CrystalDecisions.CrystalReports.Engine.TextObject textObject = Report.ReportDefinition.ReportObjects("Text5");
textObject.Color = Color.White;
}
//Report.PrintOut False, 1
//Screen.MousePointer = vbDefault
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 = "1";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
private void LoadExtraDataForView()
{
try
{
if(this._array.Count > 0)
{
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
ADOX.Catalog cat = new ADOX.Catalog();
// Open the Connection
cnn.Open(dbRoot.ConnectionString, null, null, 0);
cat.ActiveConnection = cnn;
rs.Source = cat.Views[this.View.Name].Command;
rs.Fields.Refresh();
ADODB.Fields flds = rs.Fields;
Column col = this._array[0] as Column;
f_TypeName = new DataColumn("TYPE_NAME", typeof(string));
col._row.Table.Columns.Add(f_TypeName);
f_AutoKey = new DataColumn("AUTO_INCREMENT", typeof(Boolean));
col._row.Table.Columns.Add(f_AutoKey);
Column c = null;
ADODB.Field fld = null;
int count = this._array.Count;
for( int index = 0; index < count; index++)
{
fld = flds[index];
c = (Column)this[fld.Name];
c._row["TYPE_NAME"] = fld.Type.ToString();
c._row["AUTO_INCREMENT"] = false;
}
rs.Close();
cnn.Close();
}
}
catch {}
}
public void CopyTable(ADOX.Table tblAccess)
{
ADODB.Recordset recMaster = new ADODB.Recordset();
ADODB.Recordset recLoop = new ADODB.Recordset();
int intLoop = 0;
string strInfile = "";
string strSQL = "SELECT ";
string strRecord;
string strLoadFilePath = strSourceDbPath.Replace("\\", "\\\\");
string strFileName = strTempPath + tblAccess.Name + ".txt";
StreamWriter sw = new StreamWriter(strFileName, false);
//create the infile
strInfile += "LOAD DATA LOCAL INFILE '" + strFileName + "' INTO TABLE " + strMySQLDBName + "." + tblAccess.Name + " ";
strInfile += "FIELDS TERMINATED BY ',' ";
strInfile += "ESCAPED BY '\\\\' ";
strInfile += "LINES TERMINATED BY 0x0d0a ";
strInfile += "(";
//loop through fields to enumerate them for the infile and build a select statement
for (intLoop = 0; intLoop < tblAccess.Columns.Count; intLoop++)
{
strInfile += MySQLName((tblAccess.Columns[intLoop].Name));
switch (tblAccess.Columns[intLoop].Type)
{
case ADOX.DataTypeEnum.adDate: //convert to MySQL datetime format
strSQL += "FORMAT([" + tblAccess.Columns[intLoop].Name + "], 'YYYY-MM-DD HH:MM:SS') as " + tblAccess.Columns[intLoop].Name;
break;
default:
strSQL += "[" + tblAccess.Columns[intLoop].Name + "]";
break;
}
if (intLoop < tblAccess.Columns.Count - 1)
{
strSQL += ",";
strInfile += ", ";
}
}
strInfile += ");";
strSQL += " FROM [" + tblAccess.Name + "]";
//open the "Master" recordset
recMaster.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
recMaster.Open(strSQL, conJCMS_db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0);
//create the "Loop" recordset, this is a clone of the master, with the exception
//that the definedsize for text fields is lengthened. This is because the added
//escape characters could potentially exceed the field length in the master recordset
recLoop.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
ADODB.Fields fdsLoop = recLoop.Fields;
ADODB.Fields fdsMaster = recMaster.Fields;
foreach (ADODB.Field fldIn in fdsMaster)
{
if (fldIn.Type.ToString().IndexOf("Char") > 0)
{
fdsLoop.Append(fldIn.Name,
fldIn.Type,
fldIn.DefinedSize + 30,
ADODB.FieldAttributeEnum.adFldIsNullable,
null);
}
else
{
fdsLoop.Append(fldIn.Name,
fldIn.Type,
fldIn.DefinedSize,
ADODB.FieldAttributeEnum.adFldIsNullable,
null);
}
}
recLoop.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0);
recLoop.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
while (!recMaster.EOF)
{
for (int columnIndex = 0; columnIndex < recMaster.Fields.Count; columnIndex++)
{
recLoop.Fields[columnIndex].Value = recMaster.Fields[columnIndex].Value;
if (recLoop.Fields[columnIndex].Value.ToString().Length > 0)
{
if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf("\\", 0) + 1) > 0)
{
recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace("\\", "\\\\");
}
if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(",", 0) + 1) > 0)
{
recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(",", "\\,");
}
if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(System.Environment.NewLine, 0) + 1) > 0)
{
recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(System.Environment.NewLine, " ");
}
}
}
strRecord = recLoop.GetString(ADODB.StringFormatEnum.adClipString, 1, ",", System.Environment.NewLine, "\\N");
recLoop.MovePrevious();
sw.Write(strRecord);
recMaster.MoveNext();
}
recMaster.Close();
recMaster.ActiveConnection = null;
try
{
recLoop.Close();
}
catch
{
}
sw.Close();
ExecuteSQL(strInfile);
File.Delete(strFileName);
recLoop = null;
}
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);
}
// Output data to xml
public static Boolean bExportDataToXML(String sDatabaseLocation, Boolean bDebug)
{
clsLog clsLog_ = new clsLog(msLogFile, msVersionData);
clsError clsError_ = new clsError();
ADODB.Connection conn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
clsLog_.mLog(Constants.gcInfo, "Exporting data to xml ...");
String sOneLanPropertyfile = sDatabaseLocation + "\\xml\\OneLanProperty.xml";
String sOneLanAccomfile = sDatabaseLocation + "\\xml\\OneLanAccom.xml";
if (bDebug) { clsLog_.mLog(Constants.gcInfo, sIndent + "onelan.bExportDataToXML, sOneLanPropertyfile = " + sOneLanPropertyfile); }
if (bDebug) { clsLog_.mLog(Constants.gcInfo, sIndent + "onelan.bExportDataToXML, sOneLanAccomfile = " + sOneLanAccomfile); }
try
{
// Open connection to database
conn.Open(gcDSN + sDatabaseLocation + "\\" + gsOneLanDatabase);
// Remove previous versions of the files
if (File.Exists(sOneLanPropertyfile)) {File.Delete(sOneLanPropertyfile);}
if (File.Exists(sOneLanAccomfile)) {File.Delete(sOneLanAccomfile);}
// Open the recordset
rs.Open("SELECT * FROM Property", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1);
rs.Save(sOneLanPropertyfile, ADODB.PersistFormatEnum.adPersistXML);
rs.Close();
rs.Open("SELECT * FROM Accom", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1);
rs.Save(sOneLanAccomfile, ADODB.PersistFormatEnum.adPersistXML);
rs.Close();
conn.Close();
return true;
}
catch (Exception e)
{
clsError_.mLogError("Problem exporting data to xml", "onelan", "bExportDataToXML", e, msVersionData, msLogFile, false);
return false;
}
}
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 cmdsearch_Click(System.Object eventSender, System.EventArgs eventArgs)
{
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();
if (gLoading)
return;
gLoading = true;
Cursor = System.Windows.Forms.Cursors.WaitCursor;
System.Windows.Forms.Application.DoEvents();
//lMonth = cmbMonth.ItemData(cmbMonth.ListIndex)
//If lMonth = gMonthEnd Then
databaseName = "pricing.mdb";
//Else
// databaseName = "Month" & lMonth & ".mdb"
//End If
//If openConnection() Then
// frmMain.lblPath.Caption = serverPath
// closeConnection
//End If
//Set cn = openConnectionInstance(databaseName)
//If cn Is Nothing Then
// Exit Sub
//End If
//Dim lString As String
System.Windows.Forms.Application.DoEvents();
if (rs.State)
rs.Close();
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;";
Debug.Print(sql);
//rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
rs = modRecordSet.getRS(ref sql);
lvTransaction.Items.Clear();
lblcount.Text = "0 of 0";
System.Windows.Forms.ListViewItem lvItemTran = null;
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("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) {
} else if ((Information.IsDBNull(rs.Fields("credit").Value) ? 0 : rs.Fields("credit").Value) == rs.Fields("CustomerTransaction_Allocated").Value & (Information.IsDBNull(rs.Fields("credit").Value) ? 0 : rs.Fields("credit").Value) < 0) {
} else {
lvItemTran = 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 (lvItemTran.SubItems.Count > 1) {
lvItemTran.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItemTran.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItemTran.SubItems.Count > 2) {
lvItemTran.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItemTran.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
//Select Case rs("Sale_PaymentType")
// Case 1
// lvItemTran.SubItems(2) = "Cash"
// Case 2
// lvItemTran.SubItems(2) = "CR Card"
// Case 3
// lvItemTran.SubItems(2) = "DR Card"
// Case 4
// lvItemTran.SubItems(2) = "Cheque"
// Case 5
// lvItemTran.SubItems(2) = "Account"
// Case 7
// lvItemTran.SubItems(2) = "Split Tender"
// Case Else
// lvItemTran.SubItems(2) = "Cash"
//End Select
if (lvItemTran.SubItems.Count > 3) {
lvItemTran.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItemTran.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItemTran.SubItems.Count > 4) {
lvItemTran.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItemTran.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
if (lvItemTran.SubItems.Count > 5) {
lvItemTran.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4);
} else {
lvItemTran.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4)));
}
if (lvItemTran.SubItems.Count > 6) {
lvItemTran.SubItems[6].Text = Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4);
} else {
lvItemTran.SubItems.Insert(6, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4)));
}
}
} else if (rs.Fields("TransactionType_Name").Value == "Invoice") {
if ((Information.IsDBNull(rs.Fields("debit").Value) ? 0 : rs.Fields("debit").Value) == rs.Fields("CustomerTransaction_Allocated").Value) {
} else if (rs.Fields("CustomerTransaction_Allocated").Value < 0) {
} else {
lvItemTran = 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 (lvItemTran.SubItems.Count > 1) {
lvItemTran.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItemTran.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItemTran.SubItems.Count > 2) {
lvItemTran.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItemTran.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
if (lvItemTran.SubItems.Count > 3) {
lvItemTran.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItemTran.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItemTran.SubItems.Count > 4) {
lvItemTran.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItemTran.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
if (lvItemTran.SubItems.Count > 5) {
lvItemTran.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4);
} else {
lvItemTran.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4)));
}
if (lvItemTran.SubItems.Count > 6) {
lvItemTran.SubItems[6].Text = Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4);
} else {
lvItemTran.SubItems.Insert(6, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4)));
}
}
} else if (rs.Fields("TransactionType_Name").Value == "Interest") {
if ((Information.IsDBNull(rs.Fields("debit").Value) ? 0 : rs.Fields("debit").Value) == rs.Fields("CustomerTransaction_Allocated").Value) {
} else if (rs.Fields("CustomerTransaction_Allocated").Value < 0) {
} else {
lvItemTran = 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 (lvItemTran.SubItems.Count > 1) {
lvItemTran.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItemTran.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItemTran.SubItems.Count > 2) {
lvItemTran.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItemTran.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
if (lvItemTran.SubItems.Count > 3) {
lvItemTran.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItemTran.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItemTran.SubItems.Count > 4) {
lvItemTran.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItemTran.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
if (lvItemTran.SubItems.Count > 5) {
lvItemTran.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4);
} else {
lvItemTran.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4)));
}
if (lvItemTran.SubItems.Count > 6) {
lvItemTran.SubItems[6].Text = Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4);
} else {
lvItemTran.SubItems.Insert(6, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4)));
}
}
} else if (rs.Fields("TransactionType_Name").Value == "Debit Journal") {
if ((Information.IsDBNull(rs.Fields("debit").Value) ? 0 : rs.Fields("debit").Value) == rs.Fields("CustomerTransaction_Allocated").Value) {
} else {
lvItemTran = 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 (lvItemTran.SubItems.Count > 1) {
lvItemTran.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value;
} else {
lvItemTran.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value));
}
if (lvItemTran.SubItems.Count > 2) {
lvItemTran.SubItems[2].Text = rs.Fields("TransactionType_Name").Value;
} else {
lvItemTran.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value));
}
if (lvItemTran.SubItems.Count > 3) {
lvItemTran.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4);
} else {
lvItemTran.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4)));
}
if (lvItemTran.SubItems.Count > 4) {
lvItemTran.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4);
} else {
lvItemTran.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4)));
}
if (lvItemTran.SubItems.Count > 5) {
lvItemTran.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4);
} else {
lvItemTran.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4)));
}
if (lvItemTran.SubItems.Count > 6) {
lvItemTran.SubItems[6].Text = Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4);
} else {
lvItemTran.SubItems.Insert(6, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4)));
}
}
}
rs.moveNext();
}
lvTransaction.Visible = true;
//lvTransaction.Refresh
Cursor = System.Windows.Forms.Cursors.Default;
gLoading = false;
}
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 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();
}
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);
}
}
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 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 " "; }
}
public static Boolean bExportDataToSql(String sDatabaseLocation, Boolean bDebug)
{
clsLog clsLog_ = new clsLog(msLogFile, msVersionData);
clsError clsError_ = new clsError();
ADODB.Connection conn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
clsLog_.mLog(Constants.gcInfo, "Exporting data to sql ...");
String sSql;
String sSqlfile = sDatabaseLocation + "\\data\\OneLanData.sql";
String sString = null;
String sPropertyInsertIntoHeader = null;
String sAccomInsertIntoHeader = null;
Int16 i = 0;
//String sSql = null;
// Variable to hold website information - needed to strip any single quotes out
String sWebsite = null;
if (bDebug) { clsLog_.mLog(Constants.gcInfo, sIndent + "onelan.bExportDataToSql, sSqlfile = " + sSqlfile); }
try
{
// Open connection to database
conn.Open(gcDSN + sDatabaseLocation + "\\" + gsOneLanDatabase);
// Remove previous versions of the file
if (File.Exists(sSqlfile)) { File.Delete(sSqlfile); }
// Open streamwriter obbject
StreamWriter sw = File.AppendText(sSqlfile);
// Set the insert commands
sw.WriteLine("-- File : "+ sSqlfile);
sw.WriteLine("-- Date : " + DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss"));
sw.WriteLine("-- Author : auto-sgenerated / NRE www.rmount.co.uk");
sw.WriteLine("-- Client : Raeburn Christie, Aberdeen");
sw.WriteLine("-- Purpose : Repopulate Raeburn mysql database of property data, for use in property screens");
sw.WriteLine("");
sw.WriteLine("-- To apply mysql -pusername -ppassword < " + sSqlfile);
sw.WriteLine("");
sw.WriteLine("-- Purge accom table ...");
sw.WriteLine("DELETE FROM accom;");
sw.WriteLine("-- Purge Property table ...");
sw.WriteLine("DELETE FROM property;");
sw.WriteLine("");
sw.WriteLine("-- Repopulate Property table ...");
sPropertyInsertIntoHeader = " INSERT INTO `property` (`PropID`, `PropertyAddress1`, `PropertyAddress2`, `PropertyAddress3`, `Price`, `OffersOverEtc`, `ClosingDate`, `UnderOffer`, `PropertyAddress4`, `Postcode`, `RCCWOffice`) VALUES";
sAccomInsertIntoHeader = "INSERT INTO `accom` (`PropID`, `Accom`, `Website`) VALUES";
// Open the recordset
//rs.Open("SELECT * FROM property ORDER BY PropID", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1);
sSql= "SELECT PropID" +
" , PropertyAddress1" +
" , PropertyAddress2" +
" , PropertyAddress3" +
" , Price" +
" , OffersOverEtc" +
" , Format(ClosingDate,'YYYY-MM-DD HH:NN:SS') as cd" +
" , UnderOffer" +
" , PropertyAddress4" +
" , Postcode" +
" , RCCWOffice" +
" FROM property" +
" ORDER BY PropID";
clsLog_.mLog("INFO", "property extract sql = " + sSql);
rs.Open (sSql,conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (!rs.EOF)
{
sw.WriteLine(sPropertyInsertIntoHeader);
while (!rs.EOF)
{
if (i == 150)
{
sw.WriteLine(sPropertyInsertIntoHeader);
i = 0;
}
// sString = "(" + rs.Fields["PropID"].Value + ",'" +
// rs.Fields["PropertyAddress1"].Value + "','" +
// rs.Fields["PropertyAddress2"].Value + "','" +
// rs.Fields["PropertyAddress3"].Value + "'," +
// rs.Fields["Price"].Value + ",'" +
// rs.Fields["OffersOverEtc"].Value + "','" +
// rs.Fields["ClosingDate"].Value + "','" +
// rs.Fields["UnderOffer"].Value + "','" +
// rs.Fields["PropertyAddress4"].Value + "','" +
// rs.Fields["Postcode"].Value + "','" +
// rs.Fields["RCCWOffice"].Value + "')";
sString = "(" + rs.Fields["PropID"].Value + ",'" +
rs.Fields["PropertyAddress1"].Value + "','" +
rs.Fields["PropertyAddress2"].Value + "','" +
rs.Fields["PropertyAddress3"].Value + "'," +
rs.Fields["Price"].Value + ",'" +
rs.Fields["OffersOverEtc"].Value + "','" +
rs.Fields["cd"].Value + "','" +
rs.Fields["UnderOffer"].Value + "','" +
rs.Fields["PropertyAddress4"].Value + "','" +
rs.Fields["Postcode"].Value + "','" +
rs.Fields["RCCWOffice"].Value + "')";
//sString = " i = " + i + ", PropID = " + rs.Fields["PropID"].Value;
i++;
if (i<150)
{sString = sString + ",";
}
else
{
sString = sString + ";";
}
rs.MoveNext();
// Replace '' with NULL
sString = sString.Replace("''", "NULL");
// Force mysql null date format
sString = sString.Replace("30/12/1899 00:00:00", "1899-12-30 00:00:00");
if (!rs.EOF) { sw.WriteLine(sString); }
// end while
}
// End if
}
//sw.Close();
rs.Close();
// Replace last comma with a semi colon
// Replace '' with NULL
//sString = sString.Replace("''", "NULL");
sString = sString.Remove(sString.Length - 1, 1) + ";";
// Add the last line
sw.WriteLine(sString);
//
// Now do the accom
sw.WriteLine("");
sw.WriteLine("-- Purge accom table ...");
sw.WriteLine("DELETE FROM accom;");
sw.WriteLine("");
sw.WriteLine("-- Repopulate accom table ...");
rs.Open("SELECT * FROM accom ORDER BY PropID", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (!rs.EOF)
{
sw.WriteLine(sAccomInsertIntoHeader);
while (!rs.EOF)
{
if (i == 150)
{
sw.WriteLine(sAccomInsertIntoHeader);
i = 0;
}
sWebsite = "";
sWebsite = rs.Fields["Website"].Value;
// strip any single quotes off website information
sWebsite = sWebsite.Replace("'", "");
sString = "(" + rs.Fields["PropID"].Value + "," +
"NULL" + ",'" +
sWebsite + "')";
i++;
if (i < 150)
{
sString = sString + ",";
}
else
{
sString = sString + ";";
}
rs.MoveNext();
// Replace '' with NULL
sString = sString.Replace("''", "NULL");
if (!rs.EOF) { sw.WriteLine(sString); }
// end while
}
// End if
}
//sw.Close();
rs.Close();
// Replace last comma with a semi colon
// Replace '' with NULL
sString = sString.Replace("''", "NULL");
sString = sString.Remove(sString.Length - 1, 1) + ";";
// Add the last line
sw.WriteLine(sString);
sw.Close();
return true;
}
catch (Exception e)
{
clsError_.mLogError("Problem exporting data to sql", "onelan", "bExportDataToSql", e, msVersionData, msLogFile, false);
return false;
}
// End of export data to ssql
}
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)
{
}
}
private void CustomerStatement(ref int id)
{
ADODB.Recordset rsInterest = default(ADODB.Recordset);
ADODB.Recordset rsTransaction = default(ADODB.Recordset);
ADODB.Recordset rsCompany = default(ADODB.Recordset);
int lNumber = 0;
string lAddress = null;
ADODB.Recordset rs = new ADODB.Recordset();
string sql = null;
//Dim Report As New cryCustomerStatement
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryCustomerStatement.rpt");
System.DateTime lDate = default(System.DateTime);
rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth + "));");
//rs.Open "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" & gMonth & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
//Report.txtStatementDate.SetText Format(rs("MonthEnd_Date"), "dd mmm yyyy")
Report.SetParameterValue("txtStatementDate", Strings.Format(DateAndTime.Today, "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 = DateAndTime.DateSerial(DateAndTime.Year(lDate), DateAndTime.Month(lDate), 1);
lDate = System.Date.FromOADate(lDate + rs.Fields("Company_PaymentDay").Value - 1);
//Report.txtPaymentDate.SetText 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.Database.Tables(2).SetDataSource(rsCompany);
rsTransaction = new ADODB.Recordset();
//rsTransaction.Open "SELECT CustomerTransaction.*, 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
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, 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;
}
rsInterest = new ADODB.Recordset();
rsInterest.Open("SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
//If rsInterest.BOF Or rsInterest.EOF Then
if (rsInterest.RecordCount > 0) {
//Report.Field20.Top = 280
//Report.Field21.Top = 280
//Report.Field22.Top = 280
//Report.Field23.Top = 280
Report.Database.Tables(4).SetDataSource(rsInterest);
} else {
rsInterest = new ADODB.Recordset();
rsInterest.Open("SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
//Report.Field20.Suppress = True
//Report.Field21.Suppress = True
//Report.Field22.Suppress = True
//Report.Field23.Suppress = 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
}
//Report.PrintOut(False, 1)
Report.PrintToPrinter(1, false, 0, 0);
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
private void report_WHTransfer(ref string tblName)
{
ADODB.Recordset rs = default(ADODB.Recordset);
string sql = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("cryWHRecVerify.rpt");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT Company.Company_Name FROM Company;");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
sql = "SELECT Handheld777_0.HandHeldID, StockItem.StockItem_Name, Handheld777_0.Quantity";
sql = sql + " FROM Handheld777_0 INNER JOIN StockItem ON Handheld777_0.HandHeldID = StockItem.StockItemID;";
rs = modRecordSet.getRS(ref sql);
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
ReportNone.Load("cryNoRecords.rpt");
if (rs.BOF | rs.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.Tables(1).SetDataSource(rs);
//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();
}
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();
}
}
public void TestFillDataTableWithADODBRecordSet()
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
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(stuTable, adoRS);
adoRS.Close();
adoConn.Close();
Assert.AreEqual(3, fillRowCount);
}
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 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 " "; }
}