form1.cs

上海邮区中心局门禁、考勤消费系统数据同步系统

/*
 * Created by OnlineBao.com
 * User: Samlu（鲁晓通）13918321981
 * Date: 2008-8-9
 * Time: 15:39
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using System.Data.Odbc;
using System.Text;
using System.IO;

namespace DataTrans
{
    public partial class Form1 : Form
    {
        private string FFileName = Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf("\\") + 1) + "setting.ini";
        
        public Form1()
        {
            InitializeComponent();
        }

        /// 添加GetPrivateProfileInt等6个API函数的声明和FILE_NAME常量的声明
        /// </summary>
        //declare begin  by zhanghua
        [DllImport("kernel32")]
        private static extern int GetPrivateProfileInt(
           string lpApplicationName,
           string lpKeyName,
           int nDefault,
           string lpFileName);
        [DllImport("kernel32")]
        private static extern bool GetPrivateProfileString(
          string lpApplicationName,
          string lpKeyName,
          string lpDefault,
          StringBuilder lpReturnedString,
          int nSize,
          string lpFileName);
        [DllImport("kernel32")]
        private static extern bool WritePrivateProfileString(
          string lpApplicationName,
          string lpKeyName,
          string lpString,
          string lpFileName);

        [DllImport("kernel32")]
        private static extern bool GetPrivateProfileSection(
          string lpAppName,
          StringBuilder lpReturnedString,
          int nSize,
          string lpFileName);
        [DllImport("kernel32")]
        private static extern bool WritePrivateProfileSection(
          string lpAppName,
          string lpString,
          string lpFileName);


        //参数Num为需要转换的数，n为该数的进制
        public string ToD(string Num, int n)
        {
            char[] nums = Num.ToCharArray();
            int d = 0;
            for (int i = 2; i < nums.Length; i++)
            {
                string number = nums[i].ToString();
                if (n == 16)
                {
                    switch (number.ToUpper())
                    {
                        case "A":
                            number = "10";
                            break;
                        case "B":
                            number = "11";
                            break;
                        case "C":
                            number = "12";
                            break;
                        case "D":
                            number = "13";
                            break;
                        case "E":
                            number = "14";
                            break;
                        case "F":
                            number = "15";
                            break;
                    }

                }
                Double power = Math.Pow(Convert.ToDouble(n), Convert.ToDouble(nums.Length - (i + 1)));
                d = d + Convert.ToInt32(number) * Convert.ToInt32(power);
            }
            return d.ToString();
        }

        public string ReadString(string section, string key, string def)
        {
            StringBuilder temp = new StringBuilder(1024);
            GetPrivateProfileString(section, key, def, temp, 1024, FFileName);

            return temp.ToString();
        }
        // Read DB
        private void QueryDBF()
        {
			//查询显示foxpro表里的记录
            string table = ReadString("Connection", "table path", "");
             string DSN = ReadString("Connection", "DSN", "");
            OdbcConnection conn = new System.Data.Odbc.OdbcConnection(DSN);
            conn.Close();
            conn.Open();
            //insert into d:\sqltodbf\sqltodbf\bin\debug\app_data\kaoqin.dbf (card_no,[Datetime]) values ('23456784',datetime())
           // string sql = @"select * from " + table;
            //string sql = @"insert into " + table + "(card_no,[Datetime]) values ('7777ADEF7',datetime())";
            // string sql = @"insert into " + table + "(card_no,[Datetime]) values ('" + Tcard_no + "','" + Ttime + "')";
           // OdbcCommand odbcCMD = new OdbcCommand(sql, conn);
          //  odbcCMD.ExecuteReader();


            string sql1 = @"select * from " + table;
            OdbcDataAdapter da = new OdbcDataAdapter(sql1, conn);
            // DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            //  da.Fill(ds, "table");
            da.Fill(dt);
            conn.Close();

            // this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
            this.dataGridView2.DataSource = dt.DefaultView;
            //  this.dataGridView2.DataBind();
            
        }

       



        private void LoadSys()
        {
        	//提取SQL SERVER库里两张表里的卡号新增记录，插入foxpro表里。
            int EndID_Att = Convert.ToInt32(ReadString("Connection", "EndID_Att", ""));
            int EndIDtmp_Att = Convert.ToInt32(ReadString("Connection", "EndIDtmp_Att", ""));
            int EndID_pk = Convert.ToInt32(ReadString("Connection", "EndID_pk", ""));
            int EndIDtmp_pk = Convert.ToInt32(ReadString("Connection", "EndIDtmp_pk", ""));
            string DBconn = ReadString("Connection", "DBconn", "");
            string DSN = ReadString("Connection", "DSN", "");

            SqlConnection con = new SqlConnection(DBconn);
            SqlDataAdapter dapAttTmp = new SqlDataAdapter("select RecordID,PunchNO as card_no,PunchTime as [datetime] from AttendPunch order by RecordID desc", con);
            SqlDataAdapter dapPkTmp = new SqlDataAdapter("select RecordID,CardNo as card_no,RecTime as [datetime] from PkEventRec order by RecordID desc", con);
            DataTable dtAttTmp = new DataTable();
            DataTable dtPkTmp = new DataTable();
            dapAttTmp.Fill(dtAttTmp);
            dapPkTmp.Fill(dtPkTmp);
            WritePrivateProfileString("Connection", "EndIDtmp_Att", Convert.ToString(dtAttTmp.Rows[0][0]), FFileName);
            WritePrivateProfileString("Connection", "EndIDtmp_pk", Convert.ToString(dtPkTmp.Rows[0][0]), FFileName);

            if (EndIDtmp_Att > EndID_Att || EndIDtmp_pk > EndID_pk)
            {

                SqlDataAdapter dap1 = new SqlDataAdapter("select PunchNO as card_no,PunchTime as [datetime] from AttendPunch where RecordID>" + EndID_Att + "", con);
                SqlDataAdapter dap2 = new SqlDataAdapter("select CardNo as card_no,RecTime as [datetime] from PkEventRec where RecordID>" + EndID_pk + " ", con);
                DataTable dt1 = new DataTable();
                DataTable dt2 = new DataTable();

                dap1.Fill(dt1);
                dap2.Fill(dt2);
                dt2.Merge(dt1);
                DataRow row = dt2.Rows.Add();
                // row["CardNo"]=;

                string table = ReadString("Connection", "table path", "");
                OdbcConnection conn = new System.Data.Odbc.OdbcConnection(DSN);
                conn.Open();

                for (int i = 0; i < dt2.Rows.Count-1; i++)
                {
                    // for (int j = 0; j < 2; j++)
                    // {
                    //MessageBox.Show(dt2.Rows[i][1].ToString());   //数据表i行j列的值 
                    //  }
                    //ToD(this.txtX.Text, 16);//将十六进制转换为十进制
                    string tbHexCard_no = Convert.ToString(dt2.Rows[i][0]);//卡号
                    
                    string tbDecCard_no = ToD(tbHexCard_no,16);
                    string tbDatetime = Convert.ToString(dt2.Rows[i][1]);//时间
                    listBox1.Items.Add("已传输到FOXPRO库的十进制卡号："+tbDecCard_no); //
                    string sql = @"insert into " + table + " ([card_no],[Datetime]) values ('" + tbDecCard_no + "','" + tbDatetime + "')";

                    OdbcCommand odbcCMD = new OdbcCommand(sql, conn);
                    odbcCMD.ExecuteReader();
                    
                }
                WritePrivateProfileString("Connection", "EndID_Att", Convert.ToString(EndIDtmp_Att), FFileName);
                WritePrivateProfileString("Connection", "EndID_pk", Convert.ToString(EndIDtmp_pk), FFileName);

                dataGridView1.DataSource = dt2;
                QueryDBF();
                label1.Text = (dt2.Rows.Count-1).ToString();
               
            }
            else
            {
                
            }
            con.Close();
            
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string TriggerTime = ReadString("Connection", "TriggerTime", "");
            
            timer1.Interval=Convert.ToInt32(TriggerTime)*1000;
            timer1.Start();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string DBFpath = ReadString("Connection", "DBF path", "");

            if (File.Exists(DBFpath))
                listBox1.Items.Add("网络连通，FOXPRO数据库文件存在正常。");
                
            else
                listBox1.Items.Add("网络断线或FOXPRO数据库文件不存在，请检查。");
               
           // LoadSys();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            string DBFpath = ReadString("Connection", "DBF path", "");

            if (File.Exists(DBFpath))
                LoadSys();        
            else
                listBox1.Items.Add("网络断线或FOXPRO数据库文件不存在，请检查。");
                         
        }

        

        
        void Button2Click(object sender, EventArgs e)
        {
        	listBox1.Items.Clear();
        }
    }
}