using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
class Helper
{
private static string startPath = @System.AppDomain.CurrentDomain.BaseDirectory;
#region "工具函數區"
/// <summary>
/// 得到系统时间
/// </summary>
/// <returns></returns>
public static String GetNow()
{
return DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ";
}
/// <summary>
/// 寫入LOG,自动在前面增加年月日 时分秒
/// </summary>
/// <param name="ErrorMsg"></param>
public static void ErrorLog(string ErrorMsg)
{
/*檢查Log資料夾是否存在*/
if (!Directory.Exists(Path.Combine(startPath, "Log")))
{
/*如果沒有Log資料夾,則新建一個*/
Directory.CreateDirectory(Path.Combine(startPath, "Log"));
}
/*Log檔日期格式*/
string Year = String.Format("{0:yyyy}", DateTime.Now);
string Month = String.Format("{0:MM}", DateTime.Now);
string Day = String.Format("{0:dd}", DateTime.Now);
string Time = Year + "_" + Month + "_" + Day;
/*檢查Log資料夾內的年資料夾是否存在*/
if (!Directory.Exists(Path.Combine(startPath, "Log\\" + Year)))
{
/*如果Log資料夾內的年資料夾沒有年資料夾,則新建一個*/
Directory.CreateDirectory(Path.Combine(startPath, "Log\\" + Year));
}
/*檢查Log資料夾內的年資料夾內的月資料夾是否存在*/
if (!Directory.Exists(Path.Combine(startPath, "Log\\" + Year + "\\" + Month)))
{
/*如果Log資料夾內的年資料夾的年資料夾內沒有月資料夾,則新建一個*/
Directory.CreateDirectory(Path.Combine(startPath, "Log\\" + Year + "\\" + Month));
}
/*寫入文字檔*/
/*訊息前加入時、分、秒與辨識字首*/
string LogFormat = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ==> ";
using (StreamWriter sw = new StreamWriter(Path.Combine(startPath + "\\Log\\" + Year + "\\" + Month, Time + ".log"), true))
{
sw.WriteLine(LogFormat + ErrorMsg);
}
}
#endregion
#region 每日报表
public Boolean GetDailyReportSummary(string sPlant, string sMfgType, string sStartDate, string sEndDate)
{
Boolean bResult = false;
string sSql = "";
string sSqlInsert = "";
string sTimes = "";
string sType = "";
string sQty = "";
string sQtyNg = "";
string sRate = "";
string sSqlDelete = "";
int i = 0;
DataTable dtTable = new DataTable();
string sDatabaseConnString = System.Configuration.ConfigurationSettings.AppSettings["DataBaseConnString"];
string sDatabaseSqlServer = System.Configuration.ConfigurationSettings.AppSettings["DataBaseSqlServer"];
OleDbHelper oleDbHelper = new OleDbHelper();
OleDbHelper sqlserverHelper = new OleDbHelper();
sSql += "SELECT a.times,";
sSql += " a.type,";
sSql += " a.qty,";
sSql += " nvl(b.qty_ng, 0) AS qty_ng,";
sSql += " trunc(nvl(b.qty_ng, 0) / a.qty, 3) AS rate";
sSql += " FROM (SELECT to_char(ticket_shift_date, 'yyyy-MM-dd') times,";
sSql += " type,";
sSql += " count(*) qty";
sSql += " FROM v_epticketdetail_report";
sSql += " WHERE 1 = 1";
sSql += " AND plant = '" + sPlant + "' ";
sSql += " AND segment = '" + sMfgType + "' ";
sSql += " AND ticket_shift_date > to_date('" + sStartDate + "','yyyy-MM-dd') ";
sSql += " AND ticket_shift_date< to_date('" + sEndDate + "','yyyy-MM-dd') ";
sSql += " group by ticket_shift_date, type";
sSql += " ORDER BY ticket_shift_date) a";
sSql += " LEFT JOIN (SELECT to_char(ticket_shift_date, 'yyyy-MM-dd') times,";
sSql += " type,";
sSql += " COUNT(*) qty_ng";
sSql += " FROM v_epticketdetail_report";
sSql += " WHERE 1 = 1";
sSql += " AND result = 2";
sSql += " AND plant = '" + sPlant + "' ";
sSql += " AND segment = '" + sMfgType + "' ";
sSql += " AND ticket_shift_date > to_date('" + sStartDate + "','yyyy-MM-dd') ";
sSql += " AND ticket_shift_date < to_date('" + sEndDate + "','yyyy-MM-dd') ";
sSql += " GROUP BY ticket_shift_date, type";
sSql += " ORDER BY times) b";
sSql += " on a.times = b.times";
sSql += " and a.type = b.type";
oleDbHelper.OpenConn(sDatabaseConnString);
dtTable = oleDbHelper.GetDataTable(sSql);
if (dtTable.Rows.Count > 0)
{
sqlserverHelper.OpenConn(sDatabaseSqlServer);
sSqlDelete = "delete from qc_daily_summary ";
sqlserverHelper.ExecuteNonQuery(sSqlDelete);
for (i = 0; i < dtTable.Rows.Count; i++)
{
sTimes = dtTable.Rows[i]["times"].ToString();
sType = dtTable.Rows[i]["type"].ToString();
sQty = dtTable.Rows[i]["qty"].ToString();
sQtyNg = dtTable.Rows[i]["qty_ng"].ToString();
sRate = dtTable.Rows[i]["rate"].ToString();
sSqlInsert = "insert into qc_daily_summary (times,type,qty,qty_ng,rate) values ('" + sTimes + "','" + sType + "'," + sQty + "," + sQtyNg + "," + sRate + ")";
if (sqlserverHelper.ExecuteNonQuery(sSqlInsert))
{
}
else
{
ErrorLog(sqlserverHelper.Message);
}
}
}
return bResult;
}
public Boolean GetDailyReportCategory(string sPlant, string sMfgType, string sQueryDate)
{
Boolean bResult = false;
string sSql = "";
string sSqlInsert = "";
string sReasonChinese = "";
string sQty = "";
string sQtyNg = "";
string sRate = "";
string sSqlDelete = "";
int i = 0;
DataTable dtTable = new DataTable();
string sDatabaseConnString = System.Configuration.ConfigurationSettings.AppSettings["DataBaseConnString"];
string sDatabaseSqlServer = System.Configuration.ConfigurationSettings.AppSettings["DataBaseSqlServer"];
OleDbHelper oleDbHelper = new OleDbHelper();
OleDbHelper sqlserverHelper = new OleDbHelper();
sSql += "select a.reason_chinese, ";
sSql += " nvl(count(*), 0) AS qty_ng, ";
sSql += " trunc(nvl(count(*), 0) / ";
sSql += " (SELECT count(*) qty ";
sSql += " FROM v_epticketdetail_report ";
sSql += " WHERE 1 = 1 ";
sSql += " AND plant = '" + sPlant + "' ";
sSql += " AND segment = '" + sMfgType + "' ";
sSql += " AND ticket_shift_date=to_date('" + sQueryDate + "','yyyy-MM-dd')), ";
sSql += " 5) AS rate ";
sSql += " from v_epticketdetail_report a ";
sSql += " where 1 = 1 ";
sSql += " AND result = 2 ";
sSql += " AND plant ='" + sPlant + "' ";
sSql += " AND segment = '" + sMfgType + "' ";
sSql += " AND ticket_shift_date = to_date('" + sQueryDate + "','yyyy-MM-dd') ";
sSql += " group BY reason_chinese ";
sSql += " order BY reason_chinese ";
oleDbHelper.OpenConn(sDatabaseConnString);
dtTable = oleDbHelper.GetDataTable(sSql);
if (dtTable.Rows.Count > 0)
{
sqlserverHelper.OpenConn(sDatabaseSqlServer);
sSqlDelete = "delete from qc_daily_category ";
sqlserverHelper.ExecuteNonQuery(sSqlDelete);
for (i = 0; i < dtTable.Rows.Count; i++)
{
sReasonChinese = dtTable.Rows[i]["reason_chinese"].ToString();
sQtyNg = dtTable.Rows[i]["qty_ng"].ToString();
sRate = dtTable.Rows[i]["rate"].ToString();
sSqlInsert = "insert into qc_daily_category (reason_chinese,qty_ng,rate) values ('" + sReasonChinese + "'," + sQtyNg + "," + sRate + ")";
if (sqlserverHelper.ExecuteNonQuery(sSqlInsert))
{
}
else
{
ErrorLog(sqlserverHelper.Message);
}
}
}
return bResult;
}
public Boolean GetDailyReportDRI(string sPlant, string sMfgType, string sQueryDate)
{
Boolean bResult = false;
string sSql = "";
string sSqlInsert = "";
string sTimes = "";
string sRespic = "";
string sQty = "";
string sQtyNg = "";
string sRate = "";
string sSqlDelete = "";
int i = 0;
DataTable dtTable = new DataTable();
string sDatabaseConnString = System.Configuration.ConfigurationSettings.AppSettings["DataBaseConnString"];
string sDatabaseSqlServer = System.Configuration.ConfigurationSettings.AppSettings["DataBaseSqlServer"];
OleDbHelper oleDbHelper = new OleDbHelper();
OleDbHelper sqlserverHelper = new OleDbHelper();
sSql += "select f_eportal_get_pdpic_empname(a.resppic) respic, ";
sSql += " nvl(count(*), 0) AS qty_ng, ";
sSql += " trunc(nvl(count(*), 0) / ";
sSql += " (SELECT count(*) qty ";
sSql += " FROM v_epticketdetail_report ";
sSql += " WHERE 1 = 1 ";
sSql += " AND plant = '" + sPlant + "' ";
sSql += " AND segment = '" + sMfgType + "' ";
sSql += " AND ticket_shift_date =to_date('" + sQueryDate + "','yyyy-MM-dd')), ";
sSql += " 5) AS rate ";
sSql += " from v_epticketdetail_report a ";
sSql += " where 1 = 1 ";
sSql += " and result = 2 ";
sSql += " and plant ='" + sPlant + "' ";
sSql += " and segment = '" + sMfgType + "' ";
sSql += " and ticket_shift_date = to_date('" + sQueryDate + "','yyyy-MM-dd') ";
sSql += " group BY resppic ";
sSql += " order BY resppic ";
oleDbHelper.OpenConn(sDatabaseConnString);
dtTable = oleDbHelper.GetDataTable(sSql);
if (dtTable.Rows.Count > 0)
{
sqlserverHelper.OpenConn(sDatabaseSqlServer);
sSqlDelete = "delete from qc_daily_dri ";
sqlserverHelper.ExecuteNonQuery(sSqlDelete);
for (i = 0; i < dtTable.Rows.Count; i++)
{
sRespic = dtTable.Rows[i]["respic"].ToString();
sQtyNg = dtTable.Rows[i]["qty_ng"].ToString();
sRate = dtTable.Rows[i]["rate"].ToString();
sSqlInsert = "insert into qc_daily_dri (respic,qty_ng,rate) values ('" + sRespic + "'," + sQtyNg + "," + sRate + ")";
if (sqlserverHelper.ExecuteNonQuery(sSqlInsert))
{
ErrorLog("GetDailyReportDRI同步资料成功!");
}
else
{
ErrorLog("GetDailyReportDRI同步资料失败!"+sqlserverHelper.Message);
}
}
}
return bResult;
}
public Boolean GetDailyReportDetailNG(string sPlant, string sMfgType, string sQueryDate)
{
Boolean bResult = false;
string sSql = "";
string sSqlInsert = "";
string sSqlDelete = "";
string sTimes = "";
string sType = "";
string sTicketNo = "";
string sSegment = "";
string sRegion = "";
string sRegionChinese = "";
string sLine = "";
string sModel = "";
string sStation = "";
string sSeqId = "";
string sItemDesc = "";
string sResult = "";
string sResultDesc = "";
string sRemark = "";
string sReasonCode = "";
string sReasoncodeChinese = "";
string sReasoncodeEnglish = "";
string sRegionEnglish = "";
string sErrorCode = "";
string sErrorCodeDesc = "";
string sPriority = "";
string sPriorityDesc = "";
string sImpstatus = "";
string sImpstatusDesc = "";
string sResppic = "";
string sResppicName = "";
string sPicresp = "";
string sRecomment = "";
string sMO = "";
string sTicketShiftDate = "";
string sCustomer = "";
string sCreateTime = "";
int i = 0;
DataTable dtTable = new DataTable();
string sDatabaseConnString = System.Configuration.ConfigurationSettings.AppSettings["DataBaseConnString"];
string sDatabaseSqlServer = System.Configuration.ConfigurationSettings.AppSettings["DataBaseSqlServer"];
OleDbHelper oleDbHelper = new OleDbHelper();
OleDbHelper sqlserverHelper = new OleDbHelper();
sSql += "select to_char(ticket_shift_date, 'yyyy-MM-dd') times, t.* ";
sSql += " from v_epticketdetail_report t ";
sSql += " where 1 = 1 ";
sSql += " and result = 2 ";
sSql += " and plant = '" + sPlant + "' ";
sSql += " and segment = '" + sMfgType + "' ";
sSql += " and ticket_shift_date =to_date('" + sQueryDate + "','yyyy-MM-dd') ";
oleDbHelper.OpenConn(sDatabaseConnString);
dtTable = oleDbHelper.GetDataTable(sSql);
if (dtTable.Rows.Count > 0)
{
sqlserverHelper.OpenConn(sDatabaseSqlServer);
sSqlDelete = "delete from qc_detail_ng ";
sqlserverHelper.ExecuteNonQuery(sSqlDelete);
for (i = 0; i < dtTable.Rows.Count; i++)
{
sSqlInsert = "";
try
{
sTimes = dtTable.Rows[i]["times"].ToString();
sPlant = dtTable.Rows[i]["plant"].ToString();
sTicketNo = dtTable.Rows[i]["ticketno"].ToString();
sSegment = dtTable.Rows[i]["segment"].ToString();
sRegionChinese = dtTable.Rows[i]["region_chinese"].ToString();
sRegionEnglish = dtTable.Rows[i]["region_english"].ToString();
sType = dtTable.Rows[i]["type"].ToString();
sLine = dtTable.Rows[i]["line"].ToString();
sModel = dtTable.Rows[i]["model"].ToString();
sStation = dtTable.Rows[i]["station"].ToString();
sSeqId = dtTable.Rows[i]["seq_checktypeid"].ToString();
sItemDesc = dtTable.Rows[i]["itemdesc"].ToString().Trim();
sResult = dtTable.Rows[i]["result"].ToString();
sResultDesc = dtTable.Rows[i]["result_desc"].ToString();
sRemark = dtTable.Rows[i]["remark"].ToString();
sReasonCode = dtTable.Rows[i]["reasoncode"].ToString();
sReasoncodeChinese = dtTable.Rows[i]["reason_chinese"].ToString();
sReasoncodeEnglish = dtTable.Rows[i]["reason_english"].ToString();
sErrorCode = dtTable.Rows[i]["errorcode"].ToString();
sErrorCodeDesc = dtTable.Rows[i]["errorcode_desc"].ToString();
sPriority = dtTable.Rows[i]["priority"].ToString();
sPriorityDesc = dtTable.Rows[i]["priority_desc"].ToString();
sImpstatus = dtTable.Rows[i]["impstatus"].ToString();
sImpstatusDesc = dtTable.Rows[i]["impstatus_desc"].ToString();
sResppic = dtTable.Rows[i]["resppic"].ToString();
sResppicName = dtTable.Rows[i]["resppicname"].ToString();
sPicresp = dtTable.Rows[i]["picresp"].ToString();
sRecomment = dtTable.Rows[i]["recomment"].ToString();
sMO = dtTable.Rows[i]["mo"].ToString();
sCustomer = dtTable.Rows[i]["customer"].ToString();
sStation = dtTable.Rows[i]["station"].ToString();
sTicketShiftDate = dtTable.Rows[i]["ticket_shift_date"].ToString();
sCreateTime = dtTable.Rows[i]["create_time"].ToString();
sSqlInsert += " INSERT INTO QC_DETAIL_NG";
sSqlInsert += "([plant]";
sSqlInsert += ",[ticketno]";
sSqlInsert += ",[segment]";
sSqlInsert += ",[regionid]";
sSqlInsert += ",[region_chinese]";
sSqlInsert += ",[region_english]";
sSqlInsert += ",[type]";
sSqlInsert += ",[line]";
sSqlInsert += ",[model]";
sSqlInsert += ",[seq_checktypeid]";
sSqlInsert += ",[itemdesc]";
sSqlInsert += ",[result]";
sSqlInsert += ",[result_desc]";
sSqlInsert += ",[remark]";
sSqlInsert += ",[reasoncode]";
sSqlInsert += ",[reason_english]";
sSqlInsert += ",[reason_chinese]";
sSqlInsert += ",[errorcode]";
sSqlInsert += ",[errorcode_desc]";
sSqlInsert += ",[priority]";
sSqlInsert += ",[priority_desc]";
sSqlInsert += ",[impstatus]";
sSqlInsert += ",[impstatus_desc]";
sSqlInsert += ",[resppic]";
sSqlInsert += ",[resppicname]";
sSqlInsert += ",[picresp]";
sSqlInsert += ",[recomment]";
sSqlInsert += ",[mo]";
sSqlInsert += ",[customer]";
sSqlInsert += ",[station]";
sSqlInsert += ",[ticket_shift_date]";
sSqlInsert += ",[create_time])";
sSqlInsert += "VALUES";
sSqlInsert += " ( '" + sPlant + "' ";
sSqlInsert += ",'" + sTicketNo + "' ";
sSqlInsert += ",'" + sMfgType + "' ";
sSqlInsert += ",'" + sRegion + "' ";
sSqlInsert += ",'" + sRegionChinese + "'";
sSqlInsert += ",'" + sRegionEnglish + "'";
sSqlInsert += ",'" + sType + "' ";
sSqlInsert += ",'" + sLine + "'";
sSqlInsert += ",'" + sModel + "' ";
sSqlInsert += ",'" + sSeqId + "' ";
sSqlInsert += ",'" + sItemDesc + "' ";
sSqlInsert += ",'" + sResult + "'";
sSqlInsert += ",'" + sResultDesc + "'";
sSqlInsert += ",'" + sRemark + "' ";
sSqlInsert += ",'" + sReasonCode + "' ";
sSqlInsert += ",'" + sReasoncodeEnglish + "' ";
sSqlInsert += ",'" + sReasoncodeChinese + "' ";
sSqlInsert += ",'" + sErrorCode + "'";
sSqlInsert += ",'" + sErrorCodeDesc + "' ";
sSqlInsert += ",'" + sPriority + "' ";
sSqlInsert += ",'" + sPriorityDesc + "' ";
sSqlInsert += ",'" + sImpstatus + "'";
sSqlInsert += ",'" + sImpstatusDesc + "' ";
sSqlInsert += ",'" + sResppic + "'";
sSqlInsert += ",'" + sResppicName + "'";
sSqlInsert += ",'" + sPicresp + "' ";
sSqlInsert += ",'" + sRecomment + "' ";
sSqlInsert += ",'" + sMO + "'";
sSqlInsert += ",'" + sCustomer + "' ";
sSqlInsert += ",'" + sStation + "' ";
sSqlInsert += ",'" + sTicketShiftDate + "'";
sSqlInsert += ",'" + sCreateTime + "' ";
sSqlInsert += ")";
if (sqlserverHelper.ExecuteNonQuery(sSqlInsert))
{
ErrorLog("GetDailyReportDetailNG同步资料成功!" + sTicketNo + " " + sSeqId);
}
else
{
ErrorLog("GetDailyReportDetailNG同步资料失败!"+sqlserverHelper.Message);
}
}
catch (Exception ex)
{
ErrorLog(ex.Message + ex.StackTrace + ex.Source);
}
}
}
return bResult;
}
#endregion
#region 未回复的汇总资料
/// <summary>
///
/// </summary>
/// <param name="sPlant"></param>
/// <param name="sMfgType"></param>
/// <param name="sStartDate"></param>
/// <param name="sEndDate"></param>
/// <returns></returns>
public Boolean GetUnansweredReportSummary(string sPlant, string sMfgType, string sStartDate, string sEndDate)
{
Boolean bResult = false;
string sSql = "";
string sSqlInsert = "";
string sTimes = "";
string sType = "";
string sQty = "";
string sQtyNg = "";
string sRate = "";
string sSqlDelete = "";
int i = 0;
DataTable dtTable = new DataTable();
string sDatabaseConnString = System.Configuration.ConfigurationSettings.AppSettings["DataBaseConnString"];
string sDatabaseSqlServer = System.Configuration.ConfigurationSettings.AppSettings["DataBaseSqlServer"];
OleDbHelper oleDbHelper = new OleDbHelper();
OleDbHelper sqlserverHelper = new OleDbHelper();
sSql += "select to_char(ticket_shift_date, 'yyyy-MM-dd') times, ";
sSql += " f_eportal_get_pdpic_empname(a.resppic) respic,";
sSql += " count(*) as qty ";
sSql += " from v_epticketdetail_report a ";
sSql += " where 1 = 1 ";
sSql += " and resppic is not null ";
sSql += " and picresp is null ";
sSql += " and needfb = 'Y' ";
sSql += " and plant = '" + sPlant + "' ";
sSql += " and segment = '" + sMfgType + "' ";
sSql += " and ticket_shift_date > to_date('" + sStartDate + "','yyyy-MM-dd') ";
sSql += " and ticket_shift_date< to_date('" + sEndDate + "','yyyy-MM-dd') ";
sSql += " group BY ticket_shift_date, resppic ";
sSql += " order BY ticket_shift_date, resppic ";
oleDbHelper.OpenConn(sDatabaseConnString);
dtTable = oleDbHelper.GetDataTable(sSql);
if (dtTable.Rows.Count > 0)
{
sqlserverHelper.OpenConn(sDatabaseSqlServer);
sSqlDelete = "delete from qc_unanswered_summary ";
sqlserverHelper.ExecuteNonQuery(sSqlDelete);
for (i = 0; i < dtTable.Rows.Count; i++)
{
sTimes = dtTable.Rows[i]["times"].ToString();
sType = dtTable.Rows[i]["respic"].ToString();
sQty = dtTable.Rows[i]["qty"].ToString();
sSqlInsert = "insert into qc_unanswered_summary (times,respic,qty) values ('" + sTimes + "','" + sType + "'," + sQty + ")";
if (sqlserverHelper.ExecuteNonQuery(sSqlInsert))
{
ErrorLog("GetUnansweredReportSummary同步资料成功!" + sTimes + " " + sType);
}
else
{
ErrorLog("GetUnansweredReportSummary同步资料成功!"+sqlserverHelper.Message);
}
}
}
return bResult;
}
/// <summary>
///
/// </summary>
/// <param name="sPlant"></param>
/// <param name="sMfgType"></param>
/// <param name="sStartDate"></param>
/// <param name="sEndDate"></param>
/// <returns></returns>
public Boolean GetUnansweredReportDetailNG(string sPlant, string sMfgType, string sStartDate, string sEndDate)
{
Boolean bResult = false;
string sSql = "";
string sSqlInsert = "";
string sSqlDelete = "";
string sTimes = "";
string sType = "";
string sTicketNo = "";
string sSegment = "";
string sRegion = "";
string sRegionChinese = "";
string sLine = "";
string sModel = "";
string sStation = "";
string sSeqId = "";
string sItemDesc = "";
string sResult = "";
string sResultDesc = "";
string sRemark = "";
string sReasonCode = "";
string sReasoncodeChinese = "";
string sReasoncodeEnglish = "";
string sRegionEnglish = "";
string sErrorCode = "";
string sErrorCodeDesc = "";
string sPriority = "";
string sPriorityDesc = "";
string sImpstatus = "";
string sImpstatusDesc = "";
string sResppic = "";
string sResppicName = "";
string sPicresp = "";
string sRecomment = "";
string sMO = "";
string sTicketShiftDate = "";
string sCustomer = "";
string sCreateTime = "";
int i = 0;
DataTable dtTable = new DataTable();
string sDatabaseConnString = System.Configuration.ConfigurationSettings.AppSettings["DataBaseConnString"];
string sDatabaseSqlServer = System.Configuration.ConfigurationSettings.AppSettings["DataBaseSqlServer"];
OleDbHelper oleDbHelper = new OleDbHelper();
OleDbHelper sqlserverHelper = new OleDbHelper();
sSql += "select to_char(ticket_shift_date, 'yyyy-MM-dd') times, t.* ";
sSql += " from v_epticketdetail_report t ";
sSql += " where 1 = 1 ";
sSql += " and resppic is not null ";
sSql += " and picresp is null ";
sSql += " and needfb = 'Y' ";
sSql += " and plant = '" + sPlant + "' ";
sSql += " and segment = '" + sMfgType + "' ";
sSql += " and ticket_shift_date >to_date('" + sStartDate + "','yyyy-MM-dd') ";
sSql += " and ticket_shift_date < to_date('" + sEndDate + "','yyyy-MM-dd') ";
oleDbHelper.OpenConn(sDatabaseConnString);
dtTable = oleDbHelper.GetDataTable(sSql);
if (dtTable.Rows.Count > 0)
{
sqlserverHelper.OpenConn(sDatabaseSqlServer);
sSqlDelete = "delete from QC_UNANSWERED_DETAIL ";
sqlserverHelper.ExecuteNonQuery(sSqlDelete);
for (i = 0; i < dtTable.Rows.Count; i++)
{
sSqlInsert = "";
try
{
sTimes = dtTable.Rows[i]["times"].ToString();
sPlant = dtTable.Rows[i]["plant"].ToString();
sTicketNo = dtTable.Rows[i]["ticketno"].ToString();
sSegment = dtTable.Rows[i]["segment"].ToString();
sRegionChinese = dtTable.Rows[i]["region_chinese"].ToString();
sRegionEnglish = dtTable.Rows[i]["region_english"].ToString();
sType = dtTable.Rows[i]["type"].ToString();
sLine = dtTable.Rows[i]["line"].ToString();
sModel = dtTable.Rows[i]["model"].ToString();
sStation = dtTable.Rows[i]["station"].ToString();
sSeqId = dtTable.Rows[i]["seq_checktypeid"].ToString();
sItemDesc = dtTable.Rows[i]["itemdesc"].ToString().Trim();
sResult = dtTable.Rows[i]["result"].ToString();
sResultDesc = dtTable.Rows[i]["result_desc"].ToString();
sRemark = dtTable.Rows[i]["remark"].ToString();
sReasonCode = dtTable.Rows[i]["reasoncode"].ToString();
sReasoncodeChinese = dtTable.Rows[i]["reason_chinese"].ToString();
sReasoncodeEnglish = dtTable.Rows[i]["reason_english"].ToString();
sErrorCode = dtTable.Rows[i]["errorcode"].ToString();
sErrorCodeDesc = dtTable.Rows[i]["errorcode_desc"].ToString();
sPriority = dtTable.Rows[i]["priority"].ToString();
sPriorityDesc = dtTable.Rows[i]["priority_desc"].ToString();
sImpstatus = dtTable.Rows[i]["impstatus"].ToString();
sImpstatusDesc = dtTable.Rows[i]["impstatus_desc"].ToString();
sResppic = dtTable.Rows[i]["resppic"].ToString();
sResppicName = dtTable.Rows[i]["resppicname"].ToString();
sPicresp = dtTable.Rows[i]["picresp"].ToString();
sRecomment = dtTable.Rows[i]["recomment"].ToString();
sMO = dtTable.Rows[i]["mo"].ToString();
sCustomer = dtTable.Rows[i]["customer"].ToString();
sStation = dtTable.Rows[i]["station"].ToString();
sTicketShiftDate = dtTable.Rows[i]["ticket_shift_date"].ToString();
sCreateTime = dtTable.Rows[i]["create_time"].ToString();
sSqlInsert += " INSERT INTO QC_UNANSWERED_DETAIL";
sSqlInsert += "([plant]";
sSqlInsert += ",[ticketno]";
sSqlInsert += ",[segment]";
sSqlInsert += ",[regionid]";
sSqlInsert += ",[region_chinese]";
sSqlInsert += ",[region_english]";
sSqlInsert += ",[type]";
sSqlInsert += ",[line]";
sSqlInsert += ",[model]";
sSqlInsert += ",[seq_checktypeid]";
sSqlInsert += ",[itemdesc]";
sSqlInsert += ",[result]";
sSqlInsert += ",[result_desc]";
sSqlInsert += ",[remark]";
sSqlInsert += ",[reasoncode]";
sSqlInsert += ",[reason_english]";
sSqlInsert += ",[reason_chinese]";
sSqlInsert += ",[errorcode]";
sSqlInsert += ",[errorcode_desc]";
sSqlInsert += ",[priority]";
sSqlInsert += ",[priority_desc]";
sSqlInsert += ",[impstatus]";
sSqlInsert += ",[impstatus_desc]";
sSqlInsert += ",[resppic]";
sSqlInsert += ",[resppicname]";
sSqlInsert += ",[picresp]";
sSqlInsert += ",[recomment]";
sSqlInsert += ",[mo]";
sSqlInsert += ",[customer]";
sSqlInsert += ",[station]";
sSqlInsert += ",[ticket_shift_date]";
sSqlInsert += ",[create_time])";
sSqlInsert += "VALUES";
sSqlInsert += " ( '" + sPlant + "' ";
sSqlInsert += ",'" + sTicketNo + "' ";
sSqlInsert += ",'" + sMfgType + "' ";
sSqlInsert += ",'" + sRegion + "' ";
sSqlInsert += ",'" + sRegionChinese + "'";
sSqlInsert += ",'" + sRegionEnglish + "'";
sSqlInsert += ",'" + sType + "' ";
sSqlInsert += ",'" + sLine + "'";
sSqlInsert += ",'" + sModel + "' ";
sSqlInsert += ",'" + sSeqId + "' ";
sSqlInsert += ",'" + sItemDesc + "' ";
sSqlInsert += ",'" + sResult + "'";
sSqlInsert += ",'" + sResultDesc + "'";
sSqlInsert += ",'" + sRemark + "' ";
sSqlInsert += ",'" + sReasonCode + "' ";
sSqlInsert += ",'" + sReasoncodeEnglish + "' ";
sSqlInsert += ",'" + sReasoncodeChinese + "' ";
sSqlInsert += ",'" + sErrorCode + "'";
sSqlInsert += ",'" + sErrorCodeDesc + "' ";
sSqlInsert += ",'" + sPriority + "' ";
sSqlInsert += ",'" + sPriorityDesc + "' ";
sSqlInsert += ",'" + sImpstatus + "'";
sSqlInsert += ",'" + sImpstatusDesc + "' ";
sSqlInsert += ",'" + sResppic + "'";
sSqlInsert += ",'" + sResppicName + "'";
sSqlInsert += ",'" + sPicresp + "' ";
sSqlInsert += ",'" + sRecomment + "' ";
sSqlInsert += ",'" + sMO + "'";
sSqlInsert += ",'" + sCustomer + "' ";
sSqlInsert += ",'" + sStation + "' ";
sSqlInsert += ",'" + sTicketShiftDate + "'";
sSqlInsert += ",'" + sCreateTime + "' ";
sSqlInsert += ")";
if (sqlserverHelper.ExecuteNonQuery(sSqlInsert))
{
ErrorLog("GetUnansweredReportDetailNG同步资料成功!" + sTicketNo + " " + sSeqId);
}
else
{
ErrorLog(sqlserverHelper.Message);
}
}
catch (Exception ex)
{
ErrorLog("GetUnansweredReportDetailNG" + ex.Message + ex.StackTrace + ex.Source);
}
}
}
return bResult;
}
#endregion
}