介面學習

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Web;
  4 using System.Web.Services;
  5 using System.Data;
  6 using Topevery.EOffice.Logic;
  7 using Topevery.EOffice.Entity;
  8 using System.Data.SqlClient;
  9 
 10 namespace Topevery.EOffice.Web.WebService
 11 {
 12     /// <summary>
 13     /// AttendanceMachineImport 的摘要說明
 14     /// </summary>
 15     [WebService(Namespace = "http://tempuri.org/")]
 16     [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
 17     [System.ComponentModel.ToolboxItem(false)]
 18     public class AttendanceMachineImport : System.Web.Services.WebService
 19     {
 20         /// <summary>
 21         /// 兩個列
 22         /// [DIN,員工編號][CLOCK,打卡時間]
 23         /// </summary>
 24         /// <param name="data"></param>
 25         /// <returns></returns>
 26         [WebMethod]
 27         public bool Import(DataTable data)
 28         {
 29 
 30             DataSet dsAttendanceData = new DataSet();
 31             using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["efDatabase"].ConnectionString))
 32             {
 33                 conn.Open();
 34                 using (SqlTransaction tran = conn.BeginTransaction())
 35                 {
 36                     foreach (DataRow dr in data.Rows)
 37                     {
 38                         string DIN = dr["DIN"].ToString();
 39                         DateTime Clock = (DateTime)dr["CLOCK"];
 40                         using (SqlCommand command = new SqlCommand("INSERT INTO T_EO_ATTENDANCE_MACHINE(C_DIN , C_CLOCK) VALUES (@DIN , @CLOCK);", conn, tran))
 41                         {
 42                             command.Parameters.Add(new SqlParameter("@DIN", DIN));
 43                             command.Parameters.Add(new SqlParameter("@CLOCK", Clock));
 44                             command.ExecuteNonQuery();
 45                         }
 46                     }
 47                     tran.Commit();
 48                 }
 49 
 50                 using (SqlDataAdapter command = new SqlDataAdapter("P_EO_PROCESS_ATTENDANCE_MACHINE", conn))
 51                 {
 52                     command.SelectCommand.CommandType = CommandType.StoredProcedure;
 53                     command.Fill(dsAttendanceData);
 54                 }
 55             }
 56 
 57             foreach (DataRow dr in dsAttendanceData.Tables[0].Rows)
 58             {
 59                 /*
 60                  C_USER_ID , C_DATE , C_CHECK_IN_DATE , C_CHECK_OUT_DATE
 61                  */
 62                 DateTime? _SignInDate = null; DateTime? _SignOutDate = null;
 63                 if (dr["C_CHECK_IN_DATE"] != DBNull.Value) { _SignInDate = (DateTime)dr["C_CHECK_IN_DATE"]; }
 64                 if (dr["C_CHECK_OUT_DATE"] != DBNull.Value) { _SignOutDate = (DateTime)dr["C_CHECK_OUT_DATE"]; }
 65                 EOAttendanceLogic.InsertStaffAttenceEx(new EOStaffAttence()
 66                 {
 67                     UserID = (Guid)dr["C_USER_ID"],
 68                     CheckDate = (DateTime)dr["C_DATE"],
 69                     SignInDate = _SignInDate,
 70                     SignOutDate = _SignOutDate
 71                 });
 72                 EOAvoidCheckLogic.ProcessAttendanceData((Guid)dr["C_USER_ID"], (DateTime)dr["C_DATE"]);
 73             }
 74 
 75             using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["efDatabase"].ConnectionString))
 76             {
 77                 conn.Open();
 78                 using (SqlTransaction tran = conn.BeginTransaction())
 79                 {
 80                     foreach (DataRow dr in data.Rows)
 81                     {
 82                         using (SqlCommand command = new SqlCommand("TRUNCATE TABLE T_EO_ATTENDANCE_MACHINE ;", conn, tran))
 83                         {
 84                             command.ExecuteNonQuery();
 85                         }
 86                     }
 87                     tran.Commit();
 88                 }
 89             }
 90             return true;
 91         }
 92 
 93         [WebMethod]
 94         public DateTime GetLastTime()
 95         {
 96             DateTime lastTime = DateTime.Now.AddDays(-1);
 97             string sql = @"
 98 select max(d) from(
 99 select max(a.c_sign_in_date) as d from t_eo_staff_attence_bak a 
100 inner join syn_ty_pmi_user u on a.c_user_id = u.user_id
101 where a.c_attence_type = 1
102 union all
103 select max(a.c_sign_out_date) from t_eo_staff_attence_bak a 
104 inner join syn_ty_pmi_user u on a.c_user_id = u.user_id
105 where a.c_attence_type = 1) a";
106 
107             using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["efDatabase"].ConnectionString))
108             {
109                 conn.Open();
110 
111                 using (SqlCommand command = new SqlCommand(sql, conn))
112                 {
113                     lastTime = (DateTime)command.ExecuteScalar();
114                 }
115             }
116 
117             return lastTime;
118         }
119 
120         Dictionary<string, Guid> _EoUserExtBuffer = null;
121         Dictionary<string, Guid> EoUserExtBuffer
122         {
123             get
124             {
125                 if (_EoUserExtBuffer == null)
126                 {
127                     _EoUserExtBuffer = new Dictionary<string, Guid>();
128                 }
129                 EOUserExtCollection eoUserExtColl = EOPersonalLogic.GetUserInfos();
130                 foreach (EOUserExt ext in eoUserExtColl)
131                 {
132 
133                     _EoUserExtBuffer.Add(ext.UserCode, ext.UserID);
134                 }
135 
136                 return _EoUserExtBuffer;
137             }
138         }
139        
140         
141         Guid GetUserId(string DIN)
142         {
143             if (EoUserExtBuffer.ContainsKey(DIN))
144             {
145                 return EoUserExtBuffer[DIN];
146             }
147             return Guid.Empty;
148         }
149     }
150 }

調用方式

1 TYEO.AttendanceMachineImport ser = new AttendanceMachineTool.TYEO.AttendanceMachineImport();
2             dtStart.Value = ser.GetLastTime();