public randb()
{
//
// TODO: Add constructor logic here
//
string Host = HttpContext.Current.Request.ServerVariables["server_name"].ToUpper();
if (Host.IndexOf("LOCALHOST") != -1)
objDB = new DBUtility("server=54.165.106.74;database=CIE_RandBFoods;uid=xmpieustore;pwd=uStore1;MultipleActiveResultSets=True;");
else if (Host.IndexOf("XMPIEDEV") != -1)
objDB = new DBUtility("server=172.17.0.39;database=CIE_RandBFoods;uid=xmpieustore;pwd=uStore1;MultipleActiveResultSets=True;");
else if (Host.IndexOf("STAGING") != -1)
objDB = new DBUtility("server=172.17.0.59;database=CIE_RandBFoods;uid=xmpieustore;pwd=uStore1;MultipleActiveResultSets=True;");
else
objDB = new DBUtility("server=172.17.0.38;database=CIE_RandBFoods;uid=xmpieustore;pwd=uStore1;MultipleActiveResultSets=True;");
}
protected void Page_Unload(object sender, EventArgs e)
{
DBUtility.close();
}
public static DataTable Get_proCycleDetail(string Factory_cd, string Process_cd, string BeginDate, string EndDate, string Job_Order_NO)
{
Factory_cd = Factory_cd == null ? "" : Factory_cd;
Process_cd = Process_cd == null ? "" : Process_cd;
BeginDate = BeginDate == null ? "" : BeginDate;
EndDate = EndDate == null ? "" : EndDate;
Job_Order_NO = Job_Order_NO == null ? "" : Job_Order_NO;
string SQL = "";
SQL += " select A.PROCESS_CD,A.PRODUCTION_LINE_CD,A.JOB_ORDER_NO,PO.STYLE_NO,PO.CUSTOMER_CD,B.Short_NAME as CUSTOMER_NAME,po.WASH_TYPE_CD , ";
SQL += " DBO.DATE_FORMAT(A.trx_date,'yyyy-mm-dd') TRX_DATE,SUM(ISNULL(OPENING_QTY,0)) OPENING_QTY,SUM(isnull(in_qty,0)) IN_QTY, ";
SQL += " sum(isnull(out_qty,0)) as OUT_QTY, ";
SQL += " SUM(ISNULL(POUT.DISCREPANCY_QTY,0)) AS DISCREPANCY_QTY,";
SQL += " SUM(ISNULL(POUT.PULLOUT_QTY,0)) AS PULLOUT_QTY,";
SQL += " SUM(isnull(opening_qty,0))+SUM(isnull(in_qty,0))-sum(isnull(out_qty,0)) - SUM(ISNULL(POUT.DISCREPANCY_QTY,0))-SUM(ISNULL(POUT.PULLOUT_QTY,0)) as WIP_QTY ";
SQL += " FROM (SELECT DISTINCT JOB_ORDER_NO,FACTORY_CD,DBO.DATE_FORMAT(trx_date,'yyyy-mm-dd') AS trx_date ,PROCESS_CD,PROCESS_GARMENT_TYPE,PROCESS_TYPE";//增加GARMENT_TYPE,PROCESS_TYPE列
SQL += " ,PRODUCTION_LINE_CD,SUM(ISNULL(OPENING_QTY,0)) AS OPENING_QTY";
SQL += " ,SUM(ISNULL(in_qty,0)) AS in_qty";
SQL += " ,SUM(ISNULL(out_qty,0)) AS out_qty";
SQL += " ,SUM(ISNULL(wastage_qty,0)) AS wastage_qty";
SQL += " FROM prd_jo_daily_stock WHERE 1=1 ";
SQL += " AND Factory_cd = '" + Factory_cd + "'";
if (!BeginDate.Equals(""))
{
SQL += " and trx_date >= '" + BeginDate + "' ";
}
if (!EndDate.Equals(""))
{
SQL += " and trx_date <= '" + EndDate + "' ";
}
if (!Job_Order_NO.Equals(""))
{
SQL += " AND JOB_ORDER_NO = '" + Job_Order_NO + "' ";
}
SQL += " group by job_order_no,Factory_cd,PROCESS_CD,PROCESS_GARMENT_TYPE,PROCESS_TYPE,PRODUCTION_LINE_CD, "; //Added By ZouShiChang ON 2013.08.26 MES 024 增加GARMENT_TYPE,PROCESS_TYPE列
SQL += " DBO.DATE_FORMAT(trx_date,'yyyy-mm-dd') ) AS A ";
SQL += " INNER JOIN JO_HD PO ON A.JOB_ORDER_NO=PO.JO_NO";
SQL += " INNER JOIN GEN_CUSTOMER B ON PO.CUSTOMER_CD=B.CUSTOMER_CD";
SQL += " INNER JOIN GEN_PRC_CD_MST C ON C.PRC_CD=A.PROCESS_CD AND C.GARMENT_TYPE=A.PROCESS_GARMENT_TYPE AND C.FACTORY_CD = A.FACTORY_CD AND C.END_PROCESS_FLAG IS NULL ";
SQL += " INNER JOIN (";
SQL += " SELECT DISTINCT HD.PROCESS_CD,HD.GARMENT_TYPE,HD.PROCESS_TYPE,TRX.TRX_DATE,TRX.JOB_ORDER_NO, ";//增加GARMENT_TYPE,PROCESS_TYPE列
SQL += " SUM(ISNULL(TRX.PULLOUT_QTY,0)) AS PULLOUT_QTY,SUM(ISNULL(TRX.DISCREPANCY_QTY,0)) AS DISCREPANCY_QTY";
SQL += " FROM PRD_JO_DISCREPANCY_PULLOUT_TRX TRX,PRD_JO_DISCREPANCY_PULLOUT_HD HD";
SQL += " WHERE 1=1";
SQL += " AND TRX.DOC_NO=HD.DOC_NO AND HD.FACTORY_CD = '" + Factory_cd + "'";
if (!BeginDate.Equals(""))
{
SQL += " AND TRX.trx_date >= '" + BeginDate + "' ";
}
if (!EndDate.Equals(""))
{
SQL += " AND TRX.trx_date <= '" + EndDate + "' ";
}
if (!Process_cd.Equals(""))
{
SQL += " AND PROCESS_CD = '" + Process_cd + "' ";
}
if (!Job_Order_NO.Equals(""))
{
SQL += " AND TRX.JOB_ORDER_NO = '" + Job_Order_NO + "'";
}
SQL += " GROUP BY JOB_ORDER_NO,PROCESS_CD,HD.GARMENT_TYPE,HD.PROCESS_TYPE,TRX.TRX_DATE";
SQL += " ) AS POUT ON POUT.JOB_ORDER_NO=A.JOB_ORDER_NO AND A.PROCESS_CD = POUT.PROCESS_CD AND A.PROCESS_GARMENT_TYPE=POUT.GARMENT_TYPE AND A.PROCESS_TYPE=POUT.PROCESS_TYPE";
SQL += " AND POUT.TRX_DATE = A.TRX_DATE";
SQL += " where A.JOB_ORDER_NO=PO.JO_NO ";
SQL += " AND A.factory_cd = '" + Factory_cd + "' ";
if (!Process_cd.Equals(""))
{
SQL += " and A.process_cd = '" + Process_cd + "' ";
}
if (!Job_Order_NO.Equals(""))
{
SQL += " and a.job_order_no = '" + Job_Order_NO + "' ";
}
if (!BeginDate.Equals(""))
{
SQL += " and A.trx_date >= '" + BeginDate + "' ";
}
if (!EndDate.Equals(""))
{
SQL += " and A.trx_date <= '" + EndDate + "' ";
}
SQL += " group by ";
SQL += " A.job_order_no,PO.STYLE_NO,PO.CUSTOMER_CD,A.PROCESS_CD,A.PRODUCTION_LINE_CD,B.Short_NAME,po.WASH_TYPE_CD, ";
SQL += " DBO.DATE_FORMAT(A.trx_date,'yyyy-mm-dd') ";
SQL += " order by ";
SQL += " DBO.DATE_FORMAT(A.trx_date,'yyyy-mm-dd') ";
return(DBUtility.GetTable(SQL, "MES"));
}
public List <UserProfileEditModel> GetUserSpecificDetails(Hashtable userProfileCriteria)
{
var _db = new DBUtility();
var token = new List <UserProfileEditModel>();
var _dt = new DataTable();
_cmd = new SqlCommand();
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = "GP_SP_GetUserSpecificDetails";
_cmd.Parameters.AddWithValue("@UserId", Convert.ToString(userProfileCriteria["UserID"]));
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Name"])))
{
_cmd.Parameters.AddWithValue("@Name", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Name", Convert.ToString(userProfileCriteria["Name"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["SortingOrder"])))
{
_cmd.Parameters.AddWithValue("@SortingOrder", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@SortingOrder", Convert.ToString(userProfileCriteria["SortingOrder"]).Trim());
}
_dt = _db.FillDataTable(_cmd, _dt);
if (_dt.Rows.Count > 0)
{
foreach (DataRow dr in _dt.Rows)
{
var temp = new UserProfileEditModel();
temp.ID = Convert.ToInt32(dr["ID"]);
temp.Name = Convert.ToString(dr["Name"]);
temp.Email = Convert.ToString(dr["EmailAddress"]);
temp.Address = Convert.ToString(dr["Address"]);
temp.City = Convert.ToString(dr["City"]);
temp.StateName = Convert.ToString(dr["StateName"]);
temp.ZipCode = Convert.ToInt32(dr["ZipCode"]);
temp.CompanyName = Convert.ToString(dr["CompanyName"]);
temp.LicenceNumber = Convert.ToString(dr["LicenseNumber"]);
temp.PhoneNumber = Convert.ToString(dr["PhoneNumber"]);
temp.RoleId = Convert.ToInt32(dr["RoleID"]);
temp.IsPaid = Convert.ToBoolean(DataRowExtensions.GetValue(dr, "IsPaid"));
temp.CustomerID = Convert.ToString(DataRowExtensions.GetValue(dr, "CustomerID"));
temp.ProfileImage = Convert.ToString(dr["ProfileImage"]);
temp.IsEnabled = Convert.ToBoolean(DataRowExtensions.GetValue(dr, "IsEnabled"));
temp.Gender = Convert.ToString(dr["Gender"]);
temp.CreatedDate = Convert.ToDateTime(DataRowExtensions.GetValue(dr, "CreatedDate"));
temp.ModifiedDate = Convert.ToDateTime(DataRowExtensions.GetValue(dr, "ModifiedDate"));
temp.Password = Convert.ToString(DataRowExtensions.GetValue(dr, "Password"));
temp.RoleName = Convert.ToString(DataRowExtensions.GetValue(dr, "RoleName"));
token.Add(temp);
}
}
return(token);
}
public static DataTable GetJobOrderRouteList(string factoryCd, string Date, string JoNo, string Orderby, string PartCD, string Cstr, string Process_cd, string Prod_Line_Cd, bool ShowBlankLine)
{
string SQL = " SELECT " + Cstr;
SQL = SQL + " FROM PRD_JO_ROUTE_LIST A WITH (NOLOCK) INNER JOIN GEN_OPERATION_CODE_MASTER B WITH (NOLOCK) ON A.OPER_CODE =B.OPERATION_CD AND A.FACTORY_CD = B.FACTORY_CD ";
SQL = SQL + " LEFT JOIN (SELECT S.ROUTE_DT_ID,S.LINE_CD,MAX(S.PIECE_RATE) AS PIECE_RATE FROM ";
SQL = SQL + " (SELECT E.ROUTE_DT_ID,F.LINE_CD,dbo.FN_GET_ACTUAL_PIECE_RATE(E.FACTORY_CD,E.JOB_ORDER_NO,E.JOB_CD,F.LINE_CD) AS PIECE_RATE FROM PRD_JO_ROUTE_LIST E WITH (NOLOCK) ";
SQL = SQL + " INNER JOIN PRD_GO_ROUTE_LIST_MULTI_PR F WITH (NOLOCK) ON E.ROUTE_DT_ID=F.ROUTE_DT_ID) S GROUP BY S.ROUTE_DT_ID,S.LINE_CD) C ON A.ROUTE_DT_ID=C.ROUTE_DT_ID ";
SQL = SQL + " LEFT JOIN GEN_PRODUCTION_LINE D WITH (NOLOCK) ON C.LINE_CD=D.PRODUCTION_LINE_CD ";
SQL = SQL + " LEFT JOIN PRD_JO_PROCESS_ROUTE_HD F on A.Job_Order_No=F.Job_Order_No AND PROCESS_TYPE='I' and ((A.PROCESS_CD='IRON' and F.PROCESS_CD='FIN') or (A.PROCESS_CD=F.PROCESS_CD)) ";
SQL = SQL + " WHERE A.FACTORY_CD='" + factoryCd + "' ";
if (Date != "")
{
SQL += " AND A.CREATE_DATE >= '" + Date + "' AND A.CREATE_DATE <= DATEADD(d, 1, '" + Date + "')";
}
if (JoNo != "")
{
SQL += " AND A.Job_Order_No = '" + JoNo + "' ";
}
if (PartCD != "")
{
SQL += " AND B.PART_CD = '" + PartCD + "' ";
}
if (!Process_cd.Equals("ALL"))
{
SQL += " AND A.PROCESS_CD = '" + Process_cd + "'";
}
if (Prod_Line_Cd != "")
{
if (ShowBlankLine.Equals(true))
{
SQL += " AND (C.LINE_CD='" + Prod_Line_Cd + "' OR C.LINE_CD IS NULL)";
}
else
{
SQL += " AND C.LINE_CD='" + Prod_Line_Cd + "'";
}
}
SQL += " ORDER BY A.Job_Order_No";
switch (Orderby)
{
case "DISPLAY_SEQ":
SQL += " ,CAST(A.DISPLAY_SEQ AS INT)";
break;
case "JOB_SEQUENCE_NO":
SQL += " ,CAST(A.JOB_SEQUENCE_NO AS INT)";
break;
case "JOB_CD":
SQL += " ,CAST(A.JOB_CD AS INT)";
break;
case "OPER_CODE":
SQL += " ,A.OPER_CODE";
break;
case "PROCESS_CD":
SQL += " ,F.SEQ";
break;
case "PART_CD":
SQL += " ,A.PART_CD,CAST(A.DISPLAY_SEQ AS INT)";
break;
}
return(DBUtility.GetTable(SQL, "MES"));
}
/// <summary>
/// </summary>
/// <param name="sql"></param>
/// <returns>返回值为List<Atom></returns>
private List <Atom> Select(string sql)
{
MySqlConnection connection = DBUtility.Connect();
MySqlDataReader reader = null;
MySqlCommand cmd = null;
List <Atom> list = new List <Atom>();
bool isSuccess = false;
string id = "id";
string atomName = "atomName";
string atomSymbol = "atomSymbol";
string atomEnglishName = "atomEnglishName";
string atomNumber = "atomNumber";
string relativeAtomMass = "relativeAtomMass";
string physicalProperty = "physicalProperty";
string importantKnowledge = "importantKnowledge";
string atomIntroduction = "atomIntroduction";
try
{
cmd = new MySqlCommand(sql, connection);
reader = cmd.ExecuteReader();
while (reader.Read())
{
isSuccess = true;
id = "id";
atomName = "atomName";
atomSymbol = "atomSymbol";
atomEnglishName = "atomEnglishName";
atomNumber = "atomNumber";
relativeAtomMass = "relativeAtomMass";
physicalProperty = "physicalProperty";
importantKnowledge = "importantKnowledge";
atomIntroduction = "atomIntroduction";
//Debug.Log(reader.GetInt32(id) + "," + reader.GetString(atomName) + "," + reader.GetString(atomSymbol) + "," + reader.GetString(
// atomEnglishName) + "," + reader.GetInt32(atomNumber) + "," + reader.GetDouble(relativeAtomMass) + "," + reader.GetString(physicalProperty) + "," +
// reader.GetString(importantKnowledge) + "," + reader.GetString(atomIntroduction));
list.Add(new Atom(reader.GetInt32(id), reader.GetString(atomName), reader.GetString(atomSymbol), reader.GetString(
atomEnglishName), reader.GetInt32(atomNumber), reader.GetDouble(relativeAtomMass), reader.GetString(physicalProperty),
reader.GetString(importantKnowledge), reader.GetString(atomIntroduction)));
}
if (isSuccess)
{
return(list);
}
else
{
return(null);
}
}
catch (Exception e)
{
Debug.Log("在Select时出现异常：" + e);
}
finally
{
// ReSharper disable once PossibleNullReferenceException
reader.Close();
DBUtility.CloseConnection(connection);
}
return(null);
}
private static string CreateBookOL(string isbn)
{
string resp = "";
try
{
string isbn13 = "";
string isbn13Inst = "";
if (isbn.Length == 13)
{
// the OpenLibrary service doesnt return 13 digit isbn for some reason..
// so if our request is 13, we'll send that in
isbn13 = isbn;
isbn13Inst = "'" + isbn13 + "'";
}
else
{
isbn13Inst = "NULL";
}
OLCall call = new OLCall();
string callResponse = call.MakeRequest("book", isbn);
if (callResponse == "{}")
{
// not found. send back X
resp = "X";
}
else
{
JObject book = JObject.Parse(callResponse);
string isbnKey = "ISBN:" + isbn;
JObject identifiers = (JObject)book[isbnKey]["identifiers"];
JArray isbn10Arr = (JArray)identifiers["isbn_10"];
string isbn10 = (string)isbn10Arr[0];
JArray olIdArr = (JArray)identifiers["openlibrary"];
string olId = (string)olIdArr[0];
// insert the book and get the rid back
string instQ = "INSERT INTO Book (ISBN_10,ISBN_13,ISBNDBID,ISBNDB_SOURCE) VALUES ('" + isbn10 + "'," + isbn13Inst + ",'" + olId + "','OpenLibraryAPI')";
DBUtility.ExecuteSql(instQ, "Library");
// get rid back
string getBookIdWhere = "";
switch (isbn.Length)
{
case 10:
getBookIdWhere = "WHERE ISBN_10 = '" + isbn + "'";
break;
case 13:
getBookIdWhere = "WHERE ISBN_13 = '" + isbn + "'";
break;
default:
break;
}
string getBookIdQ = "SELECT rid FROM SelectViewBook " + getBookIdWhere;
string bookRid = DBUtility.ExecuteScalar(getBookIdQ, "Library");
// now update the book with specific values
string bookTitle = (string)book[isbnKey]["title"];
JArray pubArr = (JArray)book[isbnKey]["publishers"];
string bookPubTxt = (string)pubArr[0]["name"];
string bUpdtQ = "UPDATE Book SET ";
bUpdtQ += "BookTitle = dbo.ProperCase('" + bookTitle.Replace("'", "''") + "'), ";
bUpdtQ += "PublisherText = '" + bookPubTxt.Replace("'", "''") + "' ";
bUpdtQ += "WHERE rid = " + bookRid;
DBUtility.ExecuteSql(bUpdtQ, "Library");
// now check publisher
string checkPubQ = "SELECT COUNT(*) FROM Publisher WHERE PublisherName = '" + bookPubTxt.Replace("'", "''") + "'";
int checkPub = Int32.Parse(DBUtility.ExecuteScalar(checkPubQ, "Library"));
if (checkPub > 0)
{
// we have the publisher, assign to book
string getPubRid = "SELECT TOP 1 rid FROM Publisher WHERE PublisherName = '" + bookPubTxt.Replace("'", "''") + "'";
string oldPubRid = DBUtility.ExecuteScalar(getPubRid, "Library");
bUpdtQ = "UPDATE Book SET Publisher_rid = " + oldPubRid + " WHERE rid = " + bookRid;
DBUtility.ExecuteSql(bUpdtQ, "Library");
}
else
{
// create publisher and assign to book
string instPubQ = "INSERT INTO Publisher (PublisherName) OUTPUT INSERTED.rid VALUES ('" + bookPubTxt.Replace("'", "''") + "')";
string newPubRid = DBUtility.ExecuteScalar(instPubQ, "Library");
bUpdtQ = "UPDATE Book SET Publisher_rid = " + newPubRid + " WHERE rid = " + bookRid;
DBUtility.ExecuteSql(bUpdtQ, "Library");
}
// do authors
JArray authors = (JArray)book[isbnKey]["authors"];
for (int i = 0; i < authors.Count; i++)
{
string authName = (string)authors[i]["name"];
string authNameKey = authName.Replace(" ", "");
// check author to see if we have this one
string checkAuthQ = "SELECT COUNT(*) FROM Author WHERE REPLACE(ISNULL(FirstName,'') + ISNULL(MiddleName,'') + ISNULL(LastName,''),' ','') = '" + authNameKey + "'";
int checkAuth = Int32.Parse(DBUtility.ExecuteScalar(checkAuthQ, "Library"));
if (checkAuth > 0)
{
// we have the author so get their id
string getAuthRid = "SELECT TOP 1 rid FROM Author WHERE REPLACE(ISNULL(FirstName, '') + ISNULL(MiddleName, '') + ISNULL(LastName, ''), ' ', '') = '" + authNameKey + "'";
string oldAuthRid = DBUtility.ExecuteScalar(getAuthRid, "Library");
// add the author to the book
string bookAuthQ = "INSERT INTO Book_Author (Book_rid, Author_rid) VALUES (" + bookRid + "," + oldAuthRid + ")";
DBUtility.ExecuteSql(bookAuthQ, "Library");
}
else
{
// we need to create the author
// get name
string authFName = "";
string authLName = "";
string[] authNames;
authNames = authName.Split(' ');
int authNameCt = authNames.Length;
authLName = authNames[authNameCt - 1];
for (int j = 0; j < authNameCt - 1; j++)
{
authFName += authNames[j];
}
string instAuthQ = "INSERT INTO Author (FirstName,LastName) OUTPUT INSERTED.rid VALUES ('" + authFName + "','" + authLName + "')";
string newAuthorRid = DBUtility.ExecuteScalar(instAuthQ, "Library");
string bookAuthQ = "INSERT INTO Book_Author (Book_rid, Author_rid) VALUES (" + bookRid + "," + newAuthorRid + ")";
DBUtility.ExecuteSql(bookAuthQ, "Library");
}
}
resp = bookRid;
}
}
catch (Exception e)
{
resp = "X";
}
return(resp);
}
protected void skipBtn_Click(object sender, EventArgs e)
{
int currentQuestionID = getCurrentQuestionIDFromSession();
//get extra questions list from session if it exist, if not make a new one
List <int> extraQuestions = new List <int>();
if (HttpContext.Current.Session[SESSION_EXTRA_QUESTION] != null)
{
extraQuestions = (List <int>)HttpContext.Current.Session[SESSION_EXTRA_QUESTION];
}
try
{
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
if (extraQuestions.Count <= 0)
{
SqlCommand command = new SqlCommand("SELECT * FROM Questions WHERE questionId = " + currentQuestionID, connection);
//RUN command and dump results into reader
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
//get index for the nextQuestion column
int nextQuestionColumnIndex = reader.GetOrdinal("nextQuestion");
//check if value in this row and column is NULL
if (reader.IsDBNull(nextQuestionColumnIndex))
{
List <Answers> answers = GetListOfAnswersFromSession();
foreach (Answers answer in answers)
{
//insert User details and get userId
Users users = (Users)HttpContext.Current.Session["currentUserId"];
SqlCommand commandInsertUsers = new SqlCommand("INSERT INTO Users (firstName, lastName, dob, phoneNumber, date, anonymous, ipAddress) VALUES (@firstName, @lastName, @dob, @phoneNumber, @date, @anonymous, @ipAddress); SELECT CAST(scope_identity() as int);", connection);
//add parameter
//prevents sql injection
commandInsertUsers.Parameters.Add("@firstName", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@firstName"].Value = users.firstName;
commandInsertUsers.Parameters.Add("@lastName", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@lastName"].Value = users.lastName;
commandInsertUsers.Parameters.Add("@dob", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@dob"].Value = users.dob;
commandInsertUsers.Parameters.Add("@phoneNumber", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@phoneNumber"].Value = users.phoneNumber;
commandInsertUsers.Parameters.Add("@anonymous", SqlDbType.Int, 4);
commandInsertUsers.Parameters["@anonymous"].Value = users.anon;
commandInsertUsers.Parameters.Add("@date", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@date"].Value = users.date;
commandInsertUsers.Parameters.Add("@ipAddress", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@ipAddress"].Value = users.ipAddress;
//get the userId from database
int newUserId = (int)commandInsertUsers.ExecuteScalar();
Console.WriteLine("New Product Id: " + newUserId);
SqlCommand commandInsert = new SqlCommand("INSERT INTO Answers (optionId, answerText, userId) VALUES (@optionId, @answerText, @userId);", connection);
//add parameter
//prevents sql injection
commandInsert.Parameters.Add("@optionId", SqlDbType.VarChar, 50);
commandInsert.Parameters["@optionId"].Value = answer.optionId;
if (commandInsert.Parameters["@optionId"].Value == null)
{
commandInsert.Parameters["@optionId"].Value = DBNull.Value;
}
commandInsert.Parameters.Add("@answerText", SqlDbType.VarChar, 50);
commandInsert.Parameters["@answerText"].Value = answer.answerText;
commandInsert.Parameters.Add("@userId", SqlDbType.Int, 4);
commandInsert.Parameters["@userId"].Value = newUserId;
var rowsAffected = commandInsert.ExecuteNonQuery();
if (rowsAffected <= 0)
{
//could not insert
//do something about it like show to user that the stuff didnt insert properly
Console.WriteLine("failed to write");
}
}
//empty products out of session
HttpContext.Current.Session["answers"] = null;
//clear all session
Session.Clear();
//if null, at end of survey
Response.Redirect("ThankYouPage.aspx");
}
else
{
//If not null, get the value of the nextQuestion column so we can load that question up next
int nextQuestionId = (int)reader["nextQuestion"];
//save this as the current questionId in session.
HttpContext.Current.Session["questionID"] = nextQuestionId;
HttpContext.Current.Session["currentUserId"] = currentUserId;
//reload this page
Response.Redirect("SurveyQuestions.aspx");
}
}
}
else
{
//if we do have questions on that list
//set current question to load to be equal to first question in the extraQuestions List
HttpContext.Current.Session[QUESTION_ID] = extraQuestions[0];
//add to skip button session
HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON] = extraQuestions[0];
//remove this question from the list
extraQuestions.RemoveAt(0);
//save extraQuestionlist into session
HttpContext.Current.Session[SESSION_EXTRA_QUESTION] = extraQuestions;
HttpContext.Current.Session["currentUserId"] = currentUserId;
//reload this page
Response.Redirect("SurveyQuestions.aspx");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
public string InsertIntoOutgoingEmailLog(Hashtable emailNotificationsCriteria)
{
string result = "";
DBUtility objUtility = new DBUtility();
_cmd = new SqlCommand();
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = "GP_SP_InsertIntoOutgoingEmailLog";
if (string.IsNullOrWhiteSpace(Convert.ToString(emailNotificationsCriteria["TemplateCode"])))
{
_cmd.Parameters.AddWithValue("@TemplateCode", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@TemplateCode", Convert.ToString(emailNotificationsCriteria["TemplateCode"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(emailNotificationsCriteria["EmailFrom"])))
{
_cmd.Parameters.AddWithValue("@EmailFrom", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@EmailFrom", Convert.ToString(emailNotificationsCriteria["EmailFrom"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(emailNotificationsCriteria["EmailSubject"])))
{
_cmd.Parameters.AddWithValue("@EmailSubject", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@EmailSubject", Convert.ToString(emailNotificationsCriteria["EmailSubject"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(emailNotificationsCriteria["EmailTo"])))
{
_cmd.Parameters.AddWithValue("@EmailTo", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@EmailTo", Convert.ToString(emailNotificationsCriteria["EmailTo"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(emailNotificationsCriteria["EmailBody"])))
{
_cmd.Parameters.AddWithValue("@EmailBody", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@EmailBody", Convert.ToString(emailNotificationsCriteria["EmailBody"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(emailNotificationsCriteria["Token"])))
{
_cmd.Parameters.AddWithValue("@Token", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Token", Convert.ToString(emailNotificationsCriteria["Token"]).Trim());
}
result = objUtility.ExecuteNonQuery(_cmd).ToString();
return(result);
}
public Result createUpdateBlog()
{
Result obj = new Result();
try
{
var re = Request;
var headers = re.Headers;
bool status = false;
string token = "";
if (headers.Contains("Authorization"))
{
token = headers.GetValues("Authorization").First();
status = checkToken(token);
}
if (status)
{
string imageName = null;
var httpRequest = HttpContext.Current.Request;
var postedFile = httpRequest.Files["Image"];
string postid = httpRequest["id"];
if (postedFile != null)
{
imageName = new String(Path.GetFileNameWithoutExtension(postedFile.FileName).Take(10).ToArray()).Replace(" ", "-");
imageName = imageName + DateTime.Now.ToString("yymmssfff") + Path.GetExtension(postedFile.FileName);
var filePath = HttpContext.Current.Server.MapPath("~/images/" + imageName);
postedFile.SaveAs(filePath);
}
else if (postedFile == null && postid != null)
{
imageName = getimageName(postid);
}
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
cmdParameters["title"] = new SqlParameter("title", httpRequest["title"]);
cmdParameters["category_id"] = new SqlParameter("category_id", httpRequest["category_id"]);
cmdParameters["descriptions"] = new SqlParameter("descriptions", httpRequest["description"]);
cmdParameters["imageName"] = new SqlParameter("imageName", imageName);
cmdParameters["is_featured"] = new SqlParameter("is_featured", httpRequest["is_featured"]);
cmdParameters["is_active"] = new SqlParameter("is_active", httpRequest["is_active"]);
cmdParameters["short_desc"] = new SqlParameter("short_desc", httpRequest["short_desc"]);
cmdParameters["token"] = new SqlParameter("token", token);
if (postid != null)
{
cmdParameters["state"] = new SqlParameter("state", "update");
cmdParameters["id"] = new SqlParameter("id", postid);
}
else
{
cmdParameters["state"] = new SqlParameter("state", "insert");
}
DBUtility utl = new DBUtility();
int result = utl.ExecuteCommand("USPblogs", cmdParameters);
if (result == 1)
{
obj.status = "Created or Updated";
}
else
{
obj.status = "error";
}
}
else
{
obj.status = "error";
}
}
catch (Exception ex)
{
return(null);
}
return(obj);
}
public static string SellOffer(string bidId, string sellStep, string offerId, string auctionId)
{
string resp = "";
try
{
// first make sure that the bid is still the most up to date
string currentBidQ = "SELECT Bid_rid FROM CurrentBidView WHERE OfferRid = " + offerId;
string curBidId = DBUtility.ExecuteScalar(currentBidQ, "FFA");
if (bidId != curBidId)
{
// new bid is there send back cancel message
resp = "CANCEL";
}
else
{
if (sellStep == "3")
{
// final step so sell it
string playerQ = "SELECT Player_rid FROM AuctionOffering WHERE rid = " + offerId;
string playerId = DBUtility.ExecuteScalar(playerQ, "FFA");
string teamQ = "SELECT Team_LeagueSeason_rid FROM AuctionBid WHERE rid = " + bidId;
string teamId = DBUtility.ExecuteScalar(teamQ, "FFA");
// update offer
string updtOfferQ = "UPDATE AuctionOffering SET OfferingStatus = 'Sold', WinningBid_rid = " + bidId + " WHERE rid = " + offerId;
DBUtility.ExecuteSql(updtOfferQ, "FFA");
// update bid
string updtBidQ = "UPDATE AuctionBid SET WinningBidFlag = 1 WHERE rid = " + bidId;
DBUtility.ExecuteSql(updtBidQ, "FFA");
// insert to roster
string instRosterQ = "INSERT INTO SeasonRoster (TeamLeagueSeason_rid,Player_rid,Bid_rid) VALUES (" + teamId + "," + playerId + "," + bidId + ")";
DBUtility.ExecuteSql(instRosterQ, "FFA");
// now create the new offer row
string offerQ = "SELECT * FROM OfferView WHERE OfferRid = " + offerId;
Dictionary <int, Dictionary <string, string> > offerRet = DBUtility.SqlRead(offerQ, "FFA");
Dictionary <string, string> offerRetObj = offerRet[0];
// first see if the auction is over
int roundNo = Int32.Parse(offerRetObj["LastRound"]);
int totalRounds = Int32.Parse(offerRetObj["TotalRounds"]);
if (roundNo < totalRounds)
{
// more auction to go. get the next person to offer
roundNo++;
string newOfferround = roundNo.ToString();
string teamOfferQ = "";
int previousAuctionOrder = Int32.Parse(offerRetObj["AuctionOrder"]);
int totalTeams = Int32.Parse(offerRetObj["TeamTotal"]);
if (previousAuctionOrder < totalTeams)
{
previousAuctionOrder++;
string newOfferTeam = previousAuctionOrder.ToString();
teamOfferQ = "SELECT rid FROM Team_LeagueSeason WHERE LeagueSeason_rid = " + auctionId + " AND AuctionOrder = " + newOfferTeam;
}
else
{
teamOfferQ = "SELECT rid FROM Team_LeagueSeason WHERE LeagueSeason_rid = " + auctionId + " AND AuctionOrder = 1";
}
string newTeamId = DBUtility.ExecuteScalar(teamOfferQ, "FFA");
string instOfferQ = "INSERT INTO AuctionOffering (League_Season_rid,Team_LeagueSeason_rid,OfferingStatus,AuctionRound) VALUES (" + auctionId + "," + newTeamId + ",'New'," + newOfferround + ")";
DBUtility.ExecuteSql(instOfferQ, "FFA");
}
resp = "SOLD";
}
else
{
resp = "OK";
}
}
}
catch (Exception e)
{
resp = "X";
}
return(resp);
}
public void ImportEmployeeFromCsv()
{
using (var openFileDialog1 = new OpenFileDialog()
{
Filter = "CSV|*.csv", ValidateNames = true, Multiselect = false
})
{
if (openFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
const char sepChar = ',';
const char quoteChar = '"';
var employeeList = new List <string[]>();
try
{
using (Stream stream = null)
{
var rows = File.ReadAllLines(openFileDialog1.FileName);
foreach (var csvRow in rows)
{
var inQuotes = false;
var fields = new List <string>();
var field = "";
for (var i = 0; i < csvRow.Length; i++)
{
if (inQuotes)
{
if (i < csvRow.Length - 1 && csvRow[i] == quoteChar && csvRow[i + 1] == quoteChar)
{
i = i++;
field += quoteChar;
}
else if (csvRow[i] == quoteChar)
{
inQuotes = false;
}
else
{
if (csvRow[i - 1] == quoteChar)
{
field = "";
field += csvRow[i];
}
else
{
field += csvRow[i];
}
}
}
else
{
if (csvRow[i] == quoteChar)
{
inQuotes = true;
}
if (csvRow[i] == sepChar)
{
fields.Add(field);
field = "";
}
else
{
field += csvRow[i];
}
}
}
if (!string.IsNullOrEmpty(field))
{
fields.Add(field);
field = "";
}
employeeList.Add(fields.ToArray());
}
}
}
catch (Exception er)
{
MessageBox.Show(er.Message, "Error !", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
foreach (var value in employeeList)
{
{
dataGridView.Rows.Add(value[0], value[1], value[2], value[3], value[4], value[5]);
try
{
Employee emp = new Employee(value[0], value[1], value[2], value[3], value[4], value[5]);
DBUtility.AddEmployee(emp);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error !", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
public String OneKeyLogin(String imsi)
{
StringBuilder ResponseMsg = new StringBuilder();
Result = ErrorDefinition.IError_Result_UnknowError_Code;
ErrMsg = ErrorDefinition.IError_Result_UnknowError_Msg;
wt = Request["wt"]; // json or xml
#region 数据校验
if (CommonUtility.IsEmpty(SPID))
{
// 返回错误信息
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "995");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", "SPID不能为空！");
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "995");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", "SPID不能为空！");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
Phone = getMobileSelfReg(imsi);
if (CommonUtility.IsEmpty(Phone))
{
// 返回错误信息
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "996");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", imsi + ":根据imsi号查手机号码失败！");
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "996");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", imsi + ":根据imsi号查手机号码失败！");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
#endregion
#region 开始认证
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 15;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.up_Customer_V3_Interface_IsAuthenPhone";
SqlParameter parSPID = new SqlParameter("@SPID", SqlDbType.VarChar, 8);
parSPID.Value = SPID;
cmd.Parameters.Add(parSPID);
SqlParameter parPhone = new SqlParameter("@Phone", SqlDbType.VarChar, 20);
parPhone.Value = Phone;
cmd.Parameters.Add(parPhone);
SqlParameter parResult = new SqlParameter("@Result", SqlDbType.Int);
parResult.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parResult);
SqlParameter parErrMsg = new SqlParameter("@ErrMsg ", SqlDbType.VarChar, 256);
parErrMsg.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parErrMsg);
SqlParameter parCustID = new SqlParameter("@CustID ", SqlDbType.VarChar, 16);
parCustID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parCustID);
DBUtility.Execute(cmd, DBUtility.BestToneCenterConStr);
Result = int.Parse(parResult.Value.ToString());
ErrMsg = parErrMsg.Value.ToString();
CustID = parCustID.Value.ToString();
if (!String.IsNullOrEmpty(CustID)) // 代表已经注册过，并且有认证手机
{
// 根据CustID查询客户信息并返回
String OuterID = "";
String Status = "";
String CustLevel = "";
String CertificateType = "";
String Sex = "";
String EnterpriseID = "";
String ProvinceID = "";
String Registration = "";
try
{
Result = CustBasicInfo.getCustInfo(SPID, CustID, out ErrMsg, out OuterID, out Status, out CustType,
out CustLevel, out RealName, out UserName, out NickName, out CertificateCode,
out CertificateType, out Sex, out Email, out EnterpriseID, out ProvinceID, out AreaID, out Registration);
int QueryResult = -1;
PhoneRecord[] PhoneRecords = CustBasicInfo.getPhoneRecord(CustID, out QueryResult, out ErrMsg);
ResponseMsg.Length = 0;
if (Result == 0)
{
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", Result);
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\",", ErrMsg);
ResponseMsg.AppendFormat("\"CustID\":\"{0}\",", CustID);
ResponseMsg.AppendFormat("\"Status\":\"{0}\",", Status);
ResponseMsg.AppendFormat("\"CustType\":\"{0}\",", CustType);
ResponseMsg.AppendFormat("\"CustLevel\":\"{0}\",", CustLevel);
ResponseMsg.AppendFormat("\"RealName\":\"{0}\",", RealName);
ResponseMsg.AppendFormat("\"UserName\":\"{0}\",", UserName);
ResponseMsg.AppendFormat("\"NickName\":\"{0}\",", NickName);
ResponseMsg.AppendFormat("\"CertificateCode\":\"{0}\",", CertificateCode);
ResponseMsg.AppendFormat("\"CertificateType\":\"{0}\",", CertificateType);
ResponseMsg.AppendFormat("\"Sex\":\"{0}\",", Sex);
ResponseMsg.AppendFormat("\"Email\":\"{0}\",", Email);
ResponseMsg.AppendFormat("\"EnterpriseID\":\"{0}\",", EnterpriseID);
ResponseMsg.AppendFormat("\"ProvinceID\":\"{0}\",", ProvinceID);
ResponseMsg.AppendFormat("\"AreaID\":\"{0}\",", AreaID);
if (QueryResult == 0 && PhoneRecords != null && Registration.Length > 0)
{
ResponseMsg.AppendFormat("\"Phone\":\"{0}\",", PhoneRecords[0].Phone);
ResponseMsg.AppendFormat("\"PhoneClass\":\"{0}\",", PhoneRecords[0].PhoneClass);
}
ResponseMsg.AppendFormat("\"Registration\":\"{0}\"", Registration);
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
}
catch (Exception ept)
{
// 返回错误信息
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "990");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", ept.ToString());
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "990");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", ept.ToString());
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
}
else // 没有注册过，或者注册过，但未有认证电话 ，因无法根据电话号码是否为认证电话判断客户是否注册过，因此就帮他注册一个（有可能这个用户有用户名但是没有认证手机）
{
String T_CustID = String.Empty;
if (!CommonBizRules.HasBesttoneAccount(this.Context, Phone, out T_CustID, out ErrMsg))
{
if (!String.IsNullOrEmpty(T_CustID))
{
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "990");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", Phone + "该手机号码已经被别的客户作为号码百事通账户！");
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "990");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", Phone + "该手机号码已经被别的客户作为号码百事通账户！");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
}
#region 开始注册
try
{
cmd = new SqlCommand();
cmd.CommandTimeout = 15;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.up_Customer_V3_Interface_UserRegistryClient";
parSPID = new SqlParameter("@SPID", SqlDbType.VarChar, 8);
parSPID.Value = SPID;
cmd.Parameters.Add(parSPID);
SqlParameter parUserType = new SqlParameter("@UserType", SqlDbType.VarChar, 2);
parUserType.Value = "42";
cmd.Parameters.Add(parUserType);
SqlParameter parPassword = new SqlParameter("@Password", SqlDbType.VarChar, 50);
parPassword.Value = CryptographyUtil.Encrypt("111111");
cmd.Parameters.Add(parPassword);
SqlParameter parUProvinceID = new SqlParameter("@UProvinceID", SqlDbType.VarChar, 2);
parUProvinceID.Value = "02";
cmd.Parameters.Add(parUProvinceID);
SqlParameter parAreaCode = new SqlParameter("@AreaCode", SqlDbType.VarChar, 6);
parAreaCode.Value = "021";
cmd.Parameters.Add(parAreaCode);
SqlParameter parRealName = new SqlParameter("@RealName", SqlDbType.VarChar, 50);
parRealName.Value = "";
cmd.Parameters.Add(parRealName);
SqlParameter parUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
parUserName.Value = "";
cmd.Parameters.Add(parUserName);
SqlParameter parAuthenPhone = new SqlParameter("@AuthenPhone", SqlDbType.VarChar, 15);
parAuthenPhone.Value = Phone;
cmd.Parameters.Add(parAuthenPhone);
SqlParameter parEmail = new SqlParameter("@Email", SqlDbType.VarChar, 20);
parEmail.Value = "";
cmd.Parameters.Add(parEmail);
SqlParameter parSex = new SqlParameter("@Sex", SqlDbType.VarChar, 1);
parSex.Value = "2";
cmd.Parameters.Add(parSex);
parResult = new SqlParameter("@Result", SqlDbType.Int);
parResult.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parResult);
parErrMsg = new SqlParameter("@ErrMsg ", SqlDbType.VarChar, 256);
parErrMsg.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parErrMsg);
SqlParameter paroCustID = new SqlParameter("@oCustID ", SqlDbType.VarChar, 16);
paroCustID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paroCustID);
DBUtility.Execute(cmd, DBUtility.BestToneCenterConStr);
Result = int.Parse(parResult.Value.ToString());
ErrMsg = parErrMsg.Value.ToString();
CustID = paroCustID.Value.ToString();
if (Result == 0) //注册成功
{
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", Result);
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\",", ErrMsg);
ResponseMsg.AppendFormat("\"CustID\":\"{0}\",", CustID);
ResponseMsg.AppendFormat("\"Status\":\"{0}\",", "00");
ResponseMsg.AppendFormat("\"CustType\":\"{0}\",", "42");
ResponseMsg.AppendFormat("\"CustLevel\":\"{0}\",", "3");
ResponseMsg.AppendFormat("\"RealName\":\"{0}\",", "");
ResponseMsg.AppendFormat("\"UserName\":\"{0}\",", "");
ResponseMsg.AppendFormat("\"NickName\":\"{0}\",", "");
ResponseMsg.AppendFormat("\"CertificateCode\":\"{0}\",", "");
ResponseMsg.AppendFormat("\"CertificateType\":\"{0}\",", "");
ResponseMsg.AppendFormat("\"Sex\":\"{0}\",", "2");
ResponseMsg.AppendFormat("\"Email\":\"{0}\",", "");
ResponseMsg.AppendFormat("\"EnterpriseID\":\"{0}\",", "");
ResponseMsg.AppendFormat("\"ProvinceID\":\"{0}\",", "02");
ResponseMsg.AppendFormat("\"AreaID\":\"{0}\",", "021");
ResponseMsg.AppendFormat("\"Phone\":\"{0}\",", Phone);
ResponseMsg.AppendFormat("\"Registration\":\"{0}\"", "");
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
else //注册失败
{
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "993");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", "注册失败！");
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "993");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", "注册失败!");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
}
catch (Exception ecp)
{
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "993");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", ecp.ToString());
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "993");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", ecp.ToString());
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
#endregion
}
}
catch (Exception exp)
{
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "-2508");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", exp.ToString());
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "-2508");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", exp.ToString());
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
}
#endregion
return(ResponseMsg.ToString());
}
private void DoWork(object state)
{
DBUtility.InsertInLog(0, "Service fired");
IDTPUtility.LoadMasterData(_masterDataCache, true);
}
public DataTable SelectNotification()
{
string query = @"select top 10 * from
(
select top 10
O.OrderID as ID,'An Order has been placed by '+C.Name+'.' as Data, 'Order' as Type,O.CreateDate as Date
from COrder as O
inner join Product as P on O.ProductID = P.ProductID
inner join Customer as C on P.CustomerID = C.CustomerID
order by O.OrderID desc
union
select top 10
ProductID as ID,'New Product '+Name+' has been created.' as Data, 'Product' as Type, CreateDate as Date
from Product
order by ProductID desc
union
select top 10
Q.QuotationID as ID,'Quotation Generated for '+C.Name+'.' as Data, 'Quotation' as Type, Q.CreateDate as Date
from Quotation as Q
inner join Product as P on Q.ProductID = P.ProductID
inner join Customer as C on P.CustomerID = C.CustomerID
order by QuotationID desc
union
select top 10
I.InventoryID as ID,R.Name+' placed in Inventory.' as Data, 'Inventory' as Type, I.TrDate as Date
from Inventory as I
inner join RawMaterial as R on I.RawMaterialID = R.RawMaterialID
order by I.InventoryID desc
union
select top 10
T.TransitionID as ID,P.Name+' has entered into status - '+T.NewStatus COLLATE DATABASE_DEFAULT as Data, 'Transition-Product' as Type, T.TranDate as Date
from Transition as T
inner join Product as P on T.KeyID = P.ProductID
where T.KeyType = 'Prod'
order by T.TranDate desc
union
select top 10
T.TransitionID as ID, 'Order for '+P.Name+' changed to status - '+T.NewStatus COLLATE DATABASE_DEFAULT as Data, 'Transition-Order' as Type, T.TranDate as Date
from Transition as T
inner join COrder as CO on T.KeyID = CO.OrderID
inner join Product as P on CO.ProductID = P.ProductID
where T.KeyType = 'ord'
order by T.TranDate desc
)T1
order by Date desc";
List <SqlParameter> lstParams = new List <SqlParameter>();
return(DBUtility.SelectData(query, lstParams));
}
public static DataTable[] Get_PreCut_Detail(string Go, string MO, Boolean ByLot)
{
DataTable[] tb = new DataTable[5];
string SQL = "";
SQL += " SELECT CUSTOMER_NAME,A.SIZE_CD ,SUM(ACTUAL_QTY) AS ACTUAL_QTY ";
SQL += " FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "','N') A ";
//knn
//SQL += " FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "') A ";
SQL += " inner join (select MAX(D.SHORT_NAME) as CUSTOMER_NAME,SIZE_CD,SEQ ";
SQL += " from CP_MO_HD B WITH(NOLOCK)"; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL += " INNER JOIN SC_HD C WITH(NOLOCK) ON B.GO_NO=C.SC_NO "; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL += " LEFT JOIN GEN_CUSTOMER D WITH(NOLOCK) ON C.CUSTOMER_CD=D.CUSTOMER_CD "; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL += " INNER JOIN CP_SIZE_SEQ S WITH(NOLOCK) "; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL += " on S.MO_NO=B.MO_NO ";
SQL += " where B.GO_NO='" + Go + "' ";
SQL += " AND B.MO_NO LIKE '%" + MO + "%'";
SQL += " group by SIZE_CD,SEQ ) SEQ";
SQL += " on SEQ.SIZE_CD=A.SIZE_CD ";
SQL += " WHERE 1=1";
SQL += " GROUP BY CUSTOMER_NAME,A.SIZE_CD,SEQ.SEQ ";
SQL += " ORDER BY SEQ.SEQ";
tb[0] = DBUtility.GetTable(SQL, "MES");
//TB[1]
//Modified by Jin Song MES133 (Add Color Desc)
SQL = " SELECT X.COLOR_CD,X.COLOR_DESC,Y.ORDER_QTY,X.FAB_PATTERN, ";
SQL = SQL + " (str(ROUND(Y.ADJUST_QTY*100.0/Y.ORDER_QTY,2),10,2)+'%') AS ADJUST_PERCENTS,";
SQL = SQL + " Y.ADJUST_QTY,str(Y.ACTUAL_QTY,10,2),str(X.FABRIC_QTY,10,2),CASE WHEN X.FABRIC_QTY -Y.ACTUAL_QTY/12.0* (Y.MARKER_YPD+X.BINDING_YPD)<0 then 0 ELSE X.FABRIC_QTY -Y.ACTUAL_QTY/12.0* (Y.MARKER_YPD+X.BINDING_YPD) END AS BALANCE_QTY";
SQL = SQL + " ,STR(X.RECEIVED_QTY,10,2)";
SQL = SQL + " ,STR(X.SPARE_FABRIC_QTY,10,2)";
SQL = SQL + " ,STR(X.BINDING_FABRIC_QTY,10,2)";
SQL = SQL + " ,STR(X.RECEIVED_QTY-X.BINDING_FABRIC_QTY-X.SPARE_FABRIC_QTY,10,2)";
SQL = SQL + " ,STR(X.ALLOCATED_QTY,10,2)";
SQL = SQL + " ,STR(X.RECEIVED_QTY-X.BINDING_FABRIC_QTY-X.SPARE_FABRIC_QTY-X.ALLOCATED_QTY,10,2)";
SQL = SQL + " ,str(Y.NET_YPD,10,2),str(Y.MARKER_YPD,10,2),str(X.PPO_YPD,10,2), ";
SQL = SQL + " str(Y.MARKER_UTILIZATION,10,2)+'%' ";
SQL = SQL + " ,str(Y.MARKER_WASTAGE,10,2)+'%',";
SQL = SQL + " X.FABRIC_WIDTH ";
SQL = SQL + " FROM ";
SQL = SQL + " ( ";
SQL = SQL + " SELECT A.COLOR_CD,A.COLOR_DESC,SUM(FABRIC_QTY-ISNULL(SPARE_FABRIC_QTY,0)) AS FABRIC_QTY, ";
SQL = SQL + " SUM(ISNULL(FABRIC_QTY,0)) AS RECEIVED_QTY ";
SQL = SQL + " ,SUM(ISNULL(ALLOCATED_QTY,0)) AS ALLOCATED_QTY,SUM(ISNULL(SPARE_FABRIC_QTY,0)) AS SPARE_FABRIC_QTY";
SQL = SQL + " ,SUM(ISNULL(BINDING_FABRIC_QTY,0)) AS BINDING_FABRIC_QTY,";
SQL = SQL + " MAX(A.PPO_YPD) AS PPO_YPD,MAX(ISNULL(BINDING_YPD,0)) AS BINDING_YPD,MAX(A.WIDTH) AS FABRIC_WIDTH,MAX(A.FAB_PATTERN) AS FAB_PATTERN,MAX(PERCENT_OVER_ALLOWED) AS PERCENT_OVER_ALLOWED ";
SQL = SQL + " FROM CP_FABRIC_ITEM A INNER JOIN SC_HD B ON A.GO_NO=B.SC_NO "; //By ZouShiChang ON 2013.08.29 MES024 SC_HD Change SC_HD
SQL = SQL + " WHERE 1=1";
if (!Go.Equals(""))
{
SQL = SQL + " AND A.GO_NO='" + Go + "' ";
}
SQL = SQL + " AND EXISTS(SELECT 1 FROM CP_MARKER_SET C ";
SQL = SQL + " INNER JOIN CP_MARKER D ON C.MARKER_SET_ID = D.MARKER_SET_ID ";
SQL = SQL + " INNER JOIN CP_MARKER_FABRIC E ON D.MARKER_ID = E.MARKER_ID";
SQL = SQL + " INNER JOIN CP_MO_HD F ON C.MO_NO = F.MO_NO";
SQL = SQL + " WHERE A.FAB_ITEM_ID = E.FAB_ITEM_ID AND A.GO_NO=F.GO_NO AND A.PART_TYPE_CD=F.PART_TYPE";
if (!MO.Equals(""))
{
SQL = SQL + " AND C.MO_NO ='" + MO + "'";
}
SQL = SQL + " )GROUP BY A.COLOR_CD, A.COLOR_DESC ";
SQL = SQL + " ) X ";
SQL = SQL + " INNER JOIN ";
SQL = SQL + " ( ";
SQL = SQL + " SELECT Z.COLOR_CD ,G.ORDER_QTY,G.ADJUST_QTY,G.ACTUAL_QTY ,Z.MARKER_WASTAGE, Z.MARKER_UTILIZATION, Z.NET_YPD ,Z.MARKER_YPD ";
SQL = SQL + " FROM ";
SQL = SQL + " (";
SQL = SQL + " SELECT REF_COLOR AS COLOR_CD,SUM(TOTAL_LENGTH)/SUM(GMT_QTY)*12 AS NET_YPD,SUM(ACT_QTY)/SUM(GMT_QTY)*100 AS MARKER_UTILIZATION,";
SQL = SQL + " SUM(TOTAL_LENGTH*(1+MARKER_WASTAGE*0.01))/SUM(GMT_QTY)*12 AS MARKER_YPD,";
SQL = SQL + " MAX(MARKER_WASTAGE) AS MARKER_WASTAGE";
SQL = SQL + " FROM [FN_GET_GO_COLOR_YPD]('" + Go + "','" + MO + "')";
SQL = SQL + " GROUP BY REF_COLOR";
SQL = SQL + " ) Z ";
SQL = SQL + " INNER JOIN (";
SQL = SQL + " SELECT A.COLOR_CD,SUM(A.ORDER_QTY) AS ORDER_QTY,SUM(ISNULL(ACTUAL_QTY,0)-A.ORDER_QTY) AS ADJUST_QTY,"; //change by lijer on 20161111
SQL = SQL + " SUM(ISNULL(ACTUAL_QTY,0)) AS ACTUAL_QTY FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "','N') A ";
//SQL = SQL + " SUM(ISNULL(ACTUAL_QTY,0)) AS ACTUAL_QTY FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "') A ";
SQL = SQL + " GROUP BY A.COLOR_CD ";
SQL = SQL + " ) G ON Z.COLOR_CD=G.COLOR_CD ";
SQL = SQL + " )Y ";
SQL = SQL + " ON X.COLOR_CD=Y.COLOR_CD";
tb[1] = DBUtility.GetTable(SQL, "MES");
//tb[2]
////Modified by Jin Song MES133 (Add Color Desc & PPO No)
////Bug Fix by ZK on 2014-09-17 due to double order qty when more than 1 ppo is used
//SQL = " SELECT DISTINCT C.jo_no,c.color_cd,F.COLOR_DESC,c.order_qty,D.PERCENT_SHORT ,str(c.adjust_percents,10,2)+'%',c.adjust_qty,c.ACTUAL_QTY,D.SIZE_CD,D.ACTUAL_QTY ";
////Start modification by LIMML ON 20150701 (ADD MARKER YPD AND PPO YPD)
////SQL = SQL + " AS PLAN_CUT_QTY,isnull(E.ORDER_QTY_BY_COL_SIZE,0) as ORDER_QTY_BY_COL_SIZE, F.PPO_NO";
//SQL = SQL + " AS PLAN_CUT_QTY,isnull(E.ORDER_QTY_BY_COL_SIZE,0) as ORDER_QTY_BY_COL_SIZE, F.PPO_NO, I.MARKER_YPD, I.PPO_YPD";
////End modification by LIMML ON 20150701 (ADD MARKER YPD AND PPO YPD)
//SQL = SQL + " FROM ";
//SQL = SQL + " ( ";
//SQL = SQL + " SELECT JO_NO,COLOR_CD,SUM(ORDER_QTY) AS ORDER_QTY, ";
//SQL = SQL + " ROUND(SUM(ISNULL(ADJUST_QTY,0))*100.0/SUM(ORDER_QTY),2) AS ADJUST_PERCENTS ,SUM(ISNULL(ADJUST_QTY,0)) AS ADJUST_QTY, SUM(ACTUAL_QTY) AS ACTUAL_QTY ";
//SQL = SQL + " FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "','N')";
//SQL = SQL + " GROUP BY JO_NO,COLOR_CD ";
//SQL = SQL + " ) C INNER JOIN ";
//SQL = SQL + " (SELECT JO_NO,COLOR_CD,SIZE_CD,('+'+str(MAX(PERCENT_OVER_ALLOWED),10,1)+'%/'+'-'+str(MAX(PERCENT_SHORT_ALLOWED),10,1)+'%') AS PERCENT_SHORT,SUM(ACTUAL_QTY) AS ACTUAL_QTY ";
//SQL = SQL + " FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "','N') A ";
//SQL = SQL + " LEFT JOIN SC_LOT C WITH(NOLOCK) ON A.JO_NO=C.PO_NO WHERE C.SC_NO='" + Go + "' "; //Added WITH(NOLOCK) by Jin Song 7/2014
//SQL = SQL + " GROUP BY JO_NO,COLOR_CD,SIZE_CD ";
//SQL = SQL + " )D ON C.JO_NO=D.JO_NO AND C.COLOR_CD=D.COLOR_CD ";
//SQL = SQL + " LEFT JOIN";
//SQL = SQL + " (SELECT A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')' AS SIZE_CODE,SUM(ISNULL(QTY,0)) AS ORDER_QTY_BY_COL_SIZE FROM JO_HD A WITH(NOLOCK)"; //Added WITH(NOLOCK) by Jin Song 7/2014
////新添加Order Qty;
//SQL = SQL + " INNER JOIN JO_DT B WITH(NOLOCK) ON A.JO_NO = B.JO_NO "; //Added WITH(NOLOCK) by Jin Song 7/2014
//SQL = SQL + " WHERE SC_NO = '" + Go + "'";
//SQL = SQL + " GROUP BY A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')'";
//SQL = SQL + " )E ON C.JO_NO = E.JO_NO AND C.COLOR_CD = E.COLOR_CODE AND D.SIZE_CD = E.SIZE_CODE";
//SQL = SQL + " INNER JOIN CP_FABRIC_ITEM F WITH(NOLOCK) ON F.COLOR_CD = C.COLOR_CD AND F.GO_NO = '" + Go + "'"; //Added by Jin Song MES133
////Start modification by LIMML ON 20150701 (ADD MARKER YPD AND PPO YPD)
//SQL = SQL + " INNER JOIN ( SELECT G.MO_NO, G.COLOR_CD, G.YPD AS MARKER_YPD, H.YPD AS PPO_YPD FROM CP_MO_OVER_SHORT_SUMMARY (NOLOCK) G ";
//SQL = SQL + " INNER JOIN (SELECT COLOR_CD, YPD FROM FN_GET_GO_COLOR_YPD('" + Go + "','" + MO + "'))H";
//SQL = SQL + " ON G.COLOR_CD = H.COLOR_CD WHERE G.MO_NO LIKE '%" + Go + "%') I";
//SQL = SQL + " ON I.COLOR_CD = C.COLOR_CD AND I.MO_NO LIKE '%" + Go + "%'";
////End modification by LIMML ON 20150701 (ADD MARKER YPD AND PPO YPD)
//SQL = SQL + " order by C.jo_no,color_cd";
//tb[2] = DBUtility.GetTable(SQL, "MES");
if (ByLot == true)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"
DECLARE @TEMP TABLE (COMBINE_JO NVARCHAR(20), ORIGINAL_JO NVARCHAR(20), COLOR NVARCHAR(20), SIZE NVARCHAR(20),
OVER_PER DECIMAL(38,3), SHORT_PER DECIMAL(38,3), ORDER_QTY INT)
DECLARE @ACTUAL_CUT TABLE (JO_NO NVARCHAR(20), COLOR NVARCHAR(20), SIZE NVARCHAR(20), GARMENT_TYPE NVARCHAR(1), ORDER_QTY INT, ADJUST_QTY INT, ACTUAL_QTY INT)
DECLARE @FINAL TABLE (COMBINE_JO_NO NVARCHAR(20), ORIGINAL_JO_NO NVARCHAR(20), GARMENT_TYPE NVARCHAR(1), CUT_LINE NVARCHAR(10), COLOR NVARCHAR(20), SIZE NVARCHAR(20),
ORDER_QTY INT, ACTUAL_QTY_JO_COLOR_SIZE INT, OVER_PER DECIMAL(38,3), SHORT_PER DECIMAL(38,3), DISTR_PER DECIMAL(38,3), DISTR_QTY INT, DISTR_ACTUAL_QTY INT)
DECLARE @JO_NO NVARCHAR(20),
@COLOR NVARCHAR(20),
@SIZE NVARCHAR(20),
@ACTUAL_QTY INT,
@GARMENT_TYPE NVARCHAR(1),
@CUT_LINE NVARCHAR(10),
@SUM_ORDER_QTY INT,
@OVER_SHORT_QTY INT,
@SUM_DISTR_PER DECIMAL(38,0),
@SUM_PER DECIMAL(38,0),
@TALLY INT,
@MAX DECIMAL(18,4)
INSERT INTO @TEMP
SELECT DISTINCT a.COMBINE_JO_NO AS COMBINE_JO,a.ORIGINAL_JO_NO AS ORIGINAL_JO,c.COLOR_CODE AS COLOR,
c.SIZE_CODE1 + '(' + c.SIZE_CODE2 + ')' AS SIZE, b.PERCENT_OVER_ALLOWED AS OVER_PER, b.PERCENT_SHORT_ALLOWED AS SHORT_PER, c.QTY AS ORDER_QTY
FROM JO_COMBINE_MAPPING a WITH(NOLOCK)
INNER JOIN (
SELECT PO_NO, PERCENT_OVER_ALLOWED, PERCENT_SHORT_ALLOWED FROM SC_LOT WITH(NOLOCK) WHERE SC_NO='{0}'
) b ON a.ORIGINAL_JO_NO=b.PO_NO
INNER JOIN JO_DT c WITH(NOLOCK) ON a.ORIGINAL_JO_NO=c.JO_NO
INNER JOIN JO_HD d WITH(NOLOCK) ON a.ORIGINAL_JO_NO=d.JO_NO
WHERE d.SC_NO = '{0}'
ORDER BY COLOR, SIZE
--select * from @TEMP
--Actual Cut Qty (forecast)
INSERT INTO @ACTUAL_CUT
SELECT DISTINCT B.JO_NO, B.COLOR_CD AS COLOR, B.SIZE_CD AS SIZE, A.GARMENT_TYPE_CD AS GARMENT_TYPE, SUM(B.ORDER_QTY) AS ORDER_QTY, ISNULL(SUM(B.ADJUST_QTY),0) AS ADJUST_QTY, SUM(B.ACTUAL_QTY) AS ACTUAL_QTY
FROM JO_HD A WITH(NOLOCK)
INNER JOIN (SELECT JO_NO, COLOR_CD, SIZE_CD, SUM(ORDER_QTY) AS ORDER_QTY, SUM(ISNULL(ACTUAL_QTY,0)-ORDER_QTY) AS ADJUST_QTY, SUM(ACTUAL_QTY) AS ACTUAL_QTY FROM FN_GET_GO_PRE_CUT_DETAIL('{0}','{1}','N')
GROUP BY JO_NO, COLOR_CD, SIZE_CD) B ON A.JO_NO=B.JO_NO
INNER JOIN (SELECT DISTINCT COMBINE_JO FROM @TEMP) C ON A.JO_NO=C.COMBINE_JO
WHERE A.SC_NO = '{0}'
GROUP BY B.JO_NO, B.COLOR_CD, A.GARMENT_TYPE_CD, B.SIZE_CD
ORDER BY COLOR, SIZE
--SELECT * FROM @ACTUAL_CUT
DECLARE cursorName CURSOR FOR SELECT JO_NO, COLOR, SIZE, ACTUAL_QTY, GARMENT_TYPE FROM @ACTUAL_CUT
OPEN cursorName
FETCH NEXT FROM cursorName INTO @JO_NO, @COLOR, @SIZE, @ACTUAL_QTY, @GARMENT_TYPE;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SUM_ORDER_QTY = (SELECT SUM(ORDER_QTY) FROM @TEMP WHERE COMBINE_JO=@JO_NO AND SIZE=@SIZE AND COLOR=@COLOR)
--STEP2
SET @OVER_SHORT_QTY = @ACTUAL_QTY - @SUM_ORDER_QTY
--If STEP2 > 0, use short percentage
IF @OVER_SHORT_QTY < 0 BEGIN
--STEP1
INSERT INTO @FINAL
SELECT COMBINE_JO AS COMBINE_JO_NO, ORIGINAL_JO AS ORIGINAL_JO_NO, @GARMENT_TYPE AS GARMENT_TYPE, @CUT_LINE AS CUT_LINE, COLOR, SIZE, ORDER_QTY, @ACTUAL_QTY AS ACTUAL_QTY_JO_COLOR_SIZE,
OVER_PER, SHORT_PER, 0 AS DISTR_PER, 0 AS DISTR_QTY, 0 AS DISTR_ACTUAL_QTY
FROM @TEMP
WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO=@JO_NO
--Checking min/max is it all zero%
SET @SUM_PER = (SELECT SUM(SHORT_PER) FROM @FINAL WHERE COLOR=@COLOR AND SIZE=@SIZE)
IF @SUM_PER > 0 BEGIN
--STEP3
UPDATE @FINAL SET DISTR_PER = ORDER_QTY*SHORT_PER/100 WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO
END
ELSE BEGIN --Use ratio of order_qty
UPDATE @FINAL SET DISTR_PER = ORDER_QTY/100 WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO
END
--STEP4
SET @SUM_DISTR_PER = (SELECT SUM(DISTR_PER) FROM @FINAL WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO)
UPDATE @FINAL SET DISTR_QTY = ROUND((@OVER_SHORT_QTY * DISTR_PER / (CASE WHEN @SUM_DISTR_PER=0 THEN 1 ELSE @SUM_DISTR_PER END)),0)
WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO
--STEP5
UPDATE @FINAL SET DISTR_ACTUAL_QTY = ORDER_QTY + DISTR_QTY WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO
END
--If STEP2 < 0,use over percentage
ELSE IF @OVER_SHORT_QTY > 0 BEGIN
--STEP1
INSERT INTO @FINAL
SELECT COMBINE_JO AS COMBINE_JO_NO, ORIGINAL_JO AS ORIGINAL_JO_NO, @GARMENT_TYPE AS GARMENT_TYPE, @CUT_LINE AS CUT_LINE, COLOR, SIZE, ORDER_QTY, @ACTUAL_QTY AS ACTUAL_QTY_JO_COLOR_SIZE,
OVER_PER, SHORT_PER, 0 AS DISTR_PER, 0 AS DISTR_QTY, 0 AS DISTR_ACTUAL_QTY
FROM @TEMP
WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO=@JO_NO
--Checking min/max is it all zero%
SET @SUM_PER = (SELECT SUM(OVER_PER) FROM @FINAL WHERE COLOR=@COLOR AND SIZE=@SIZE)
IF @SUM_PER > 0 BEGIN
--STEP3
UPDATE @FINAL SET DISTR_PER = ORDER_QTY*OVER_PER/100 WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO
END
ELSE BEGIN
UPDATE @FINAL SET DISTR_PER = ORDER_QTY WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO
END
--SELECT * FROM @FINAL
--STEP4
SET @SUM_DISTR_PER = (SELECT SUM(DISTR_PER) FROM @FINAL WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO)
UPDATE @FINAL SET DISTR_QTY = ROUND((@OVER_SHORT_QTY * DISTR_PER / (CASE WHEN @SUM_DISTR_PER=0 THEN 1 ELSE @SUM_DISTR_PER END)),0)
WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO
--STEP5
UPDATE @FINAL SET DISTR_ACTUAL_QTY = ORDER_QTY + DISTR_QTY WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO
END
ELSE BEGIN
--If STEP2 = 0, direct use order qty
INSERT INTO @FINAL
SELECT COMBINE_JO AS COMBINE_JO_NO, ORIGINAL_JO AS ORIGINAL_JO_NO, @GARMENT_TYPE AS GARMENT_TYPE, @CUT_LINE AS CUT_LINE, COLOR, SIZE, ORDER_QTY, @ACTUAL_QTY AS ACTUAL_QTY_JO_COLOR_SIZE,
OVER_PER, SHORT_PER, 0 AS DISTR_PER, 0 AS DISTR_QTY, ORDER_QTY AS DISTR_ACTUAL_QTY
FROM @TEMP
WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO=@JO_NO
END
--Checking before final
SET @TALLY = (SELECT SUM(DISTR_ACTUAL_QTY) FROM @FINAL WHERE COLOR=@COLOR AND SIZE=@SIZE AND COMBINE_JO_NO=@JO_NO)
IF (@TALLY - @ACTUAL_QTY <> 0) BEGIN
IF @OVER_SHORT_QTY > 0 BEGIN
SET @MAX = (SELECT MAX(DISTR_PER) FROM @FINAL WHERE COMBINE_JO_NO=@JO_NO AND COLOR=@COLOR AND SIZE=@SIZE)
END
ELSE IF @OVER_SHORT_QTY < 0 BEGIN
SET @MAX = (SELECT MAX(DISTR_PER) FROM @FINAL WHERE COMBINE_JO_NO=@JO_NO AND COLOR=@COLOR AND SIZE=@SIZE)
END
ELSE BEGIN
SET @MAX = (SELECT MAX(ORDER_QTY) FROM @FINAL WHERE COMBINE_JO_NO=@JO_NO AND COLOR=@COLOR AND SIZE=@SIZE)
END
IF @OVER_SHORT_QTY = 0 BEGIN
UPDATE @FINAL SET DISTR_ACTUAL_QTY = DISTR_ACTUAL_QTY + (@ACTUAL_QTY - @TALLY)
WHERE COMBINE_JO_NO=@JO_NO AND ORIGINAL_JO_NO=(SELECT TOP 1 ORIGINAL_JO_NO FROM @FINAL WHERE ORDER_QTY=@MAX AND COMBINE_JO_NO=@JO_NO
AND COLOR=@COLOR AND SIZE=@SIZE) AND COLOR=@COLOR AND SIZE=@SIZE
END
ELSE BEGIN
UPDATE @FINAL SET DISTR_ACTUAL_QTY = DISTR_ACTUAL_QTY + (@ACTUAL_QTY - @TALLY)
WHERE COMBINE_JO_NO=@JO_NO AND ORIGINAL_JO_NO=(SELECT TOP 1 ORIGINAL_JO_NO FROM @FINAL WHERE DISTR_PER=@MAX AND COMBINE_JO_NO=@JO_NO
AND COLOR=@COLOR AND SIZE=@SIZE) AND COLOR=@COLOR AND SIZE=@SIZE
END
END
FETCH NEXT FROM cursorName INTO @JO_NO, @COLOR, @SIZE, @ACTUAL_QTY, @GARMENT_TYPE;
END
CLOSE cursorName
DEALLOCATE cursorName
--SELECT * FROM @FINAL
(SELECT DISTINCT A.COMBINE_JO_NO AS JO_NO, A.ORIGINAL_JO_NO AS LOT_NO, A.COLOR AS COLOR_CD, B.COLOR_DESC, C.SUM_ORDER AS ORDER_QTY, ('+'+STR(A.OVER_PER,10,1)+'%/'+'-'+STR(A.SHORT_PER,10,1)+'%') AS PERCENT_SHORT,
str(ROUND(((F.DISTR_QTY - F.ORDER_QTY)*100.0/F.ORDER_QTY),2),10,2)+'%' AS ADJUST_PERCENT ,C.SUM_ACTUAL-C.SUM_ORDER AS ADJUST_QTY, C.SUM_ACTUAL AS ACTUAL_QTY, A.SIZE AS SIZE_CD,
A.DISTR_ACTUAL_QTY AS PLAN_CUT_QTY, A.ORDER_QTY AS ORDER_QTY_BY_COL_SIZE, B.PPO_NO, I.MARKER_YPD, I.PPO_YPD
FROM @FINAL A
INNER JOIN CP_FABRIC_ITEM B WITH(NOLOCK) ON A.COLOR=B.COLOR_CD AND B.GO_NO='{0}'
INNER JOIN (SELECT COMBINE_JO_NO, ORIGINAL_JO_NO, COLOR, SUM(DISTR_ACTUAL_QTY) AS SUM_ACTUAL, SUM(ORDER_QTY) AS SUM_ORDER FROM @FINAL GROUP BY COMBINE_JO_NO, ORIGINAL_JO_NO, COLOR) C ON A.COMBINE_JO_NO=C.COMBINE_JO_NO AND A.ORIGINAL_JO_NO=C.ORIGINAL_JO_NO AND A.COLOR=C.COLOR
INNER JOIN (
SELECT G.MO_NO, G.COLOR_CD, G.YPD AS MARKER_YPD, H.YPD AS PPO_YPD FROM CP_MO_OVER_SHORT_SUMMARY (NOLOCK) G
INNER JOIN
(SELECT REF_COLOR, YPD
FROM FN_GET_GO_COLOR_YPD('{0}','{1}'))H ON G.COLOR_CD = H.REF_COLOR WHERE G.MO_NO LIKE '%{0}%'
) I ON I.COLOR_CD = A.COLOR AND I.MO_NO LIKE '%{0}%'
INNER JOIN (SELECT PO_NO, CONVERT(NVARCHAR(5),LOT_NO) AS LOT_NO FROM SC_LOT WITH(NOLOCK) WHERE SC_NO='{0}') E ON E.PO_NO=A.ORIGINAL_JO_NO
INNER JOIN (SELECT DISTINCT COMBINE_JO_NO, ORIGINAL_JO_NO, COLOR, SUM(ORDER_QTY) AS ORDER_QTY, SUM(DISTR_ACTUAL_QTY) AS DISTR_QTY
FROM @FINAL GROUP BY COMBINE_JO_NO, ORIGINAL_JO_NO, COLOR) F ON A.ORIGINAL_JO_NO=F.ORIGINAL_JO_NO AND A.COLOR=F.COLOR
)
UNION ALL
(
SELECT DISTINCT C.jo_no ,'' as lot_no,c.color_cd,F.COLOR_DESC,c.order_qty,
CASE WHEN C.jo_no like '%CB%' THEN ('+' + str(j.PERCENT_OVER_ALLOWED,10,1)+'%/-' + str(j.PERCENT_SHORT_ALLOWED,10,1) + '%') ELSE D.PERCENT_SHORT END ,
str(c.adjust_percents,10,2)+'%', c.adjust_qty,c.ACTUAL_QTY,D.SIZE_CD,D.ACTUAL_QTY AS PLAN_CUT_QTY,isnull(E.ORDER_QTY_BY_COL_SIZE,0) as ORDER_QTY_BY_COL_SIZE,
F.PPO_NO, I.MARKER_YPD, I.PPO_YPD
FROM ( SELECT JO_NO,COLOR_CD,SUM(ORDER_QTY) AS ORDER_QTY,
ROUND(SUM(ISNULL(ACTUAL_QTY,0)-ORDER_QTY)*100.0/SUM(ORDER_QTY),2) AS ADJUST_PERCENTS ,SUM(ISNULL(ACTUAL_QTY,0)-ORDER_QTY) AS ADJUST_QTY,
SUM(ACTUAL_QTY) AS ACTUAL_QTY
FROM FN_GET_GO_PRE_CUT_DETAIL('{0}','{1}','N') GROUP BY JO_NO,COLOR_CD ) C
INNER JOIN (SELECT JO_NO,COLOR_CD,SIZE_CD,CASE WHEN JO_NO LIKE '%CB%' THEN '' ELSE ('+'+str(MAX(PERCENT_OVER_ALLOWED),10,1)+'%/'+'-'+str(MAX(PERCENT_SHORT_ALLOWED),10,1)+'%') END AS PERCENT_SHORT,
SUM(ACTUAL_QTY) AS ACTUAL_QTY FROM FN_GET_GO_PRE_CUT_DETAIL('{0}','{1}','N') A
LEFT JOIN (SELECT PO_NO,PERCENT_OVER_ALLOWED,PERCENT_SHORT_ALLOWED FROM SC_LOT WITH(NOLOCK) WHERE SC_NO='{0}') C ON A.JO_NO=C.PO_NO
GROUP BY JO_NO,COLOR_CD,SIZE_CD )D ON C.JO_NO=D.JO_NO AND C.COLOR_CD=D.COLOR_CD
LEFT JOIN (SELECT A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')' AS SIZE_CODE,SUM(ISNULL(QTY,0)) AS ORDER_QTY_BY_COL_SIZE
FROM JO_HD A WITH(NOLOCK) INNER JOIN JO_DT B WITH(NOLOCK) ON A.JO_NO = B.JO_NO
WHERE SC_NO = '{0}'
GROUP BY A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')' )E ON C.JO_NO = E.JO_NO AND C.COLOR_CD = E.COLOR_CODE AND D.SIZE_CD = E.SIZE_CODE
INNER JOIN CP_FABRIC_ITEM F WITH(NOLOCK) ON F.COLOR_CD = C.COLOR_CD AND F.GO_NO = '{0}'
INNER JOIN
( SELECT G.MO_NO, G.COLOR_CD, G.YPD AS MARKER_YPD, H.YPD AS PPO_YPD FROM CP_MO_OVER_SHORT_SUMMARY (NOLOCK) G
INNER JOIN
(SELECT REF_COLOR, YPD
FROM FN_GET_GO_COLOR_YPD('{0}','{1}'))H ON G.COLOR_CD = H.REF_COLOR WHERE G.MO_NO LIKE '%{0}%') I ON I.COLOR_CD = C.COLOR_CD AND I.MO_NO
LIKE '%{0}%'
LEFT JOIN
(SELECT A.COMBINE_JO_NO,CAST(ROUND(((SUM(B.TOTAL_QTY*(1+C.PERCENT_OVER_ALLOWED/100))/SUM(B.TOTAL_QTY))-1)*100,2)AS DECIMAL(38,2)) AS PERCENT_OVER_ALLOWED,
CAST(ROUND(((SUM(B.TOTAL_QTY*(1-C.PERCENT_SHORT_ALLOWED/100))/SUM(B.TOTAL_QTY))-1)*-100,2)AS DECIMAL(38,2)) AS PERCENT_SHORT_ALLOWED
FROM JO_COMBINE_MAPPING A WITH(NOLOCK)
INNER JOIN JO_HD B WITH(NOLOCK) ON A.ORIGINAL_JO_NO = B.JO_NO
INNER JOIN SC_LOT C WITH(NOLOCK) ON B.SC_NO = C.SC_NO AND B.LOT_NO = C.LOT_NO
WHERE B.SC_NO='{0}'
GROUP BY A.COMBINE_JO_NO) J ON J.COMBINE_JO_NO=C.jo_no
WHERE NOT EXISTS(SELECT ORIGINAL_JO_NO FROM JO_COMBINE_MAPPING WITH(NOLOCK) WHERE ORIGINAL_JO_NO=C.JO_NO)
) order by JO_NO, COLOR_CD, LOT_NO
", Go, MO);
tb[2] = MESComment.DBUtility.GetTable(sb.ToString(), "MES");
}
else
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"
(SELECT DISTINCT C.jo_no,c.color_cd,F.COLOR_DESC,c.order_qty,D.PERCENT_SHORT ,str(c.adjust_percents,10,2)+'%',
c.adjust_qty,c.ACTUAL_QTY,D.SIZE_CD,D.ACTUAL_QTY AS PLAN_CUT_QTY,isnull(E.ORDER_QTY_BY_COL_SIZE,0) as ORDER_QTY_BY_COL_SIZE,
F.PPO_NO, I.MARKER_YPD, I.PPO_YPD
FROM
(SELECT JO_NO,COLOR_CD,SUM(ORDER_QTY) AS ORDER_QTY, ROUND(SUM(ISNULL(ACTUAL_QTY,0)-ORDER_QTY)*100.0/SUM(ORDER_QTY),2) AS ADJUST_PERCENTS ,
SUM(ISNULL(ACTUAL_QTY,0)-ORDER_QTY) AS ADJUST_QTY, SUM(ACTUAL_QTY) AS ACTUAL_QTY
FROM FN_GET_GO_PRE_CUT_DETAIL('{0}','{1}','N') GROUP BY JO_NO,COLOR_CD ) C
INNER JOIN (SELECT JO_NO,COLOR_CD,SIZE_CD,
('+'+str(MAX(PERCENT_OVER_ALLOWED),10,1)+'%/'+'-'+str(MAX(PERCENT_SHORT_ALLOWED),10,1)+'%') AS PERCENT_SHORT,
SUM(ACTUAL_QTY) AS ACTUAL_QTY
FROM FN_GET_GO_PRE_CUT_DETAIL('{0}','{1}','N') A
LEFT JOIN (
SELECT A.COMBINE_JO_NO,CAST(ROUND(((SUM(B.TOTAL_QTY*(1+C.PERCENT_OVER_ALLOWED/100))/SUM(B.TOTAL_QTY))-1)*100,2)AS DECIMAL(38,2)) AS PERCENT_OVER_ALLOWED,
CAST(ROUND(((SUM(B.TOTAL_QTY*(1-C.PERCENT_SHORT_ALLOWED/100))/SUM(B.TOTAL_QTY))-1)*-100,2)AS DECIMAL(38,2)) AS PERCENT_SHORT_ALLOWED
FROM JO_COMBINE_MAPPING A WITH(NOLOCK)
INNER JOIN JO_HD B WITH(NOLOCK) ON A.ORIGINAL_JO_NO = B.JO_NO
INNER JOIN SC_LOT C WITH(NOLOCK) ON B.SC_NO = C.SC_NO AND B.LOT_NO = C.LOT_NO
WHERE B.SC_NO='{0}'
GROUP BY A.COMBINE_JO_NO
) LOT ON LOT.COMBINE_JO_NO=A.JO_NO
GROUP BY JO_NO,COLOR_CD,SIZE_CD )D ON C.JO_NO=D.JO_NO AND C.COLOR_CD=D.COLOR_CD
LEFT JOIN (SELECT A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')' AS SIZE_CODE,SUM(ISNULL(QTY,0)) AS ORDER_QTY_BY_COL_SIZE
FROM JO_HD A WITH(NOLOCK) INNER JOIN JO_DT B WITH(NOLOCK) ON A.JO_NO = B.JO_NO
WHERE SC_NO = '{0}' GROUP BY A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')' )E ON C.JO_NO = E.JO_NO
AND C.COLOR_CD = E.COLOR_CODE AND D.SIZE_CD = E.SIZE_CODE
INNER JOIN CP_FABRIC_ITEM F WITH(NOLOCK) ON F.COLOR_CD = C.COLOR_CD AND F.GO_NO = '{0}'
INNER JOIN ( SELECT G.MO_NO, G.COLOR_CD, G.YPD AS MARKER_YPD, H.YPD AS PPO_YPD
FROM CP_MO_OVER_SHORT_SUMMARY (NOLOCK) G
INNER JOIN (SELECT REF_COLOR, YPD FROM FN_GET_GO_COLOR_YPD('{0}','{1}'))H ON G.COLOR_CD = H.REF_COLOR
WHERE G.MO_NO LIKE '%{0}%') I ON I.COLOR_CD = C.COLOR_CD AND I.MO_NO LIKE '%{0}%'
AND C.JO_NO LIKE '%CB%')
UNION ALL
(
SELECT DISTINCT C.jo_no,c.color_cd,F.COLOR_DESC,c.order_qty,D.PERCENT_SHORT ,
str(c.adjust_percents,10,2)+'%',c.adjust_qty,c.ACTUAL_QTY,D.SIZE_CD,D.ACTUAL_QTY AS PLAN_CUT_QTY,
isnull(E.ORDER_QTY_BY_COL_SIZE,0) as ORDER_QTY_BY_COL_SIZE, F.PPO_NO, I.MARKER_YPD, I.PPO_YPD
FROM (
SELECT JO_NO,COLOR_CD,SUM(ORDER_QTY) AS ORDER_QTY, ROUND(SUM(ISNULL(ACTUAL_QTY,0)-ORDER_QTY)*100.0/SUM(ORDER_QTY),2) AS ADJUST_PERCENTS ,
SUM(ISNULL(ACTUAL_QTY,0)-ORDER_QTY) AS ADJUST_QTY, SUM(ACTUAL_QTY) AS ACTUAL_QTY FROM FN_GET_GO_PRE_CUT_DETAIL('{0}','{1}','N')
GROUP BY JO_NO,COLOR_CD) C
INNER JOIN (
SELECT JO_NO,COLOR_CD,SIZE_CD,('+'+str(MAX(PERCENT_OVER_ALLOWED),10,1)+'%/'+'-'+str(MAX(PERCENT_SHORT_ALLOWED),10,1)+'%')
AS PERCENT_SHORT,SUM(ACTUAL_QTY) AS ACTUAL_QTY FROM FN_GET_GO_PRE_CUT_DETAIL('{0}','{1}','N') A
LEFT JOIN SC_LOT C WITH(NOLOCK) ON A.JO_NO=C.PO_NO WHERE C.SC_NO='{0}'
GROUP BY JO_NO,COLOR_CD,SIZE_CD )D ON C.JO_NO=D.JO_NO AND C.COLOR_CD=D.COLOR_CD
LEFT JOIN (
SELECT A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')' AS SIZE_CODE,SUM(ISNULL(QTY,0)) AS ORDER_QTY_BY_COL_SIZE
FROM JO_HD A WITH(NOLOCK)
INNER JOIN JO_DT B WITH(NOLOCK) ON A.JO_NO = B.JO_NO
WHERE SC_NO = '{0}'
GROUP BY A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')' )E ON C.JO_NO = E.JO_NO AND C.COLOR_CD = E.COLOR_CODE
AND D.SIZE_CD = E.SIZE_CODE
INNER JOIN CP_FABRIC_ITEM F WITH(NOLOCK) ON F.COLOR_CD = C.COLOR_CD AND F.GO_NO = '{0}'
INNER JOIN ( SELECT G.MO_NO, G.COLOR_CD, G.YPD AS MARKER_YPD, H.YPD AS PPO_YPD
FROM CP_MO_OVER_SHORT_SUMMARY (NOLOCK) G
INNER JOIN (SELECT REF_COLOR, YPD FROM FN_GET_GO_COLOR_YPD('{0}','{1}'))H ON G.COLOR_CD = H.REF_COLOR
WHERE G.MO_NO LIKE '%{0}%') I ON I.COLOR_CD = C.COLOR_CD AND I.MO_NO LIKE '%{0}%'
AND NOT EXISTS(SELECT ORIGINAL_JO_NO FROM JO_COMBINE_MAPPING WITH(NOLOCK) WHERE ORIGINAL_JO_NO=C.JO_NO))
order by jo_no,color_cd", Go, MO);
tb[2] = MESComment.DBUtility.GetTable(sb.ToString(), "MES");
}
SQL = "SELECT DISTINCT C.SIZE_CD,A.SEQ FROM CP_SIZE_SEQ A,dbo.CP_MO_HD B,CP_MO_CS_BREAKDOWN C WHERE B.GO_NO='" + Go + "'";
SQL += " AND A.MO_NO LIKE '%" + MO + "%' AND A.MO_NO=B.MO_NO AND B.MO_NO=C.MO_NO AND A.SIZE_CD=C.SIZE_CD ORDER BY 2";
tb[3] = DBUtility.GetTable(SQL, "MES");
SQL = " select JOB_ORDER_NO,COLOR,[SIZE]+''+SIZE2 as size,REMARK ";
SQL = SQL + " From dbo.PRD_JO_REMARKS A, JO_HD B ";
SQL = SQL + " where remark_type in('CUTPLAN_PPC','CUTPLAN_CUT') ";
SQL = SQL + " AND A.FACTORY_CD=B.FACTORY_CD ";
SQL = SQL + " AND A.JOB_ORDER_NO=B.JO_NO ";
SQL = SQL + " AND B.SC_NO ='" + Go + "' ";
SQL = SQL + " ORDER BY JOB_ORDER_NO,REMARK_TYPE,COLOR,[SIZE],SIZE2 ";
tb[4] = DBUtility.GetTable(SQL, "MES");
return(tb);
}
protected void Page_Load(object sender, EventArgs e)
{
skipBtn.Style["visibility"] = "hidden";
Console.WriteLine(HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON]);
try
{
currentUserId = (int)HttpContext.Current.Session["currentUserId"];
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
//GetIpAddress(out ipAddress);
if (AppSession.isLoggedIn())
{
titleSurvey.Text = "Welcome " + AppSession.getUsername();
}
//get current questionID
int currentQuestionID = getCurrentQuestionIDFromSession();
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
SqlCommand command = new SqlCommand("SELECT * FROM Questions WHERE questionId = " + currentQuestionID, connection);
//RUN command and dump results into reader
SqlDataReader reader = command.ExecuteReader();
//must do one read to gete onto first row of results in reader (can only show 1 question per page, so 1 read is all we need)
if (reader.Read())
{
//get question text and put it in our label
string questionText = (string)reader["questionText"];
questionLabel.Text = questionText;
//makes all the value in the reader typeName to lowercase
string questionType = ((string)reader["questionType"]).ToLower();
if (questionType == "textbox")
{
TextBox textBox = new TextBox();
textBox.ID = "questionTextBox";
QuestionPlaceHolder.Controls.Add(textBox);
}
else if (questionType == "checkbox")
{
CheckBoxList checkBoxList = new CheckBoxList();
checkBoxList.ID = "questionCheckBox";
//get options associated with this current question and dump into checkBoxList
SqlCommand optionCommand = new SqlCommand("SELECT * FROM Options WHERE questionId = " + currentQuestionID, connection);
SqlDataReader optionReader = optionCommand.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReader.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReader["optionText"], optionReader["optionId"].ToString());
checkBoxList.Items.Add(item);
}
QuestionPlaceHolder.Controls.Add(checkBoxList);
HttpContext.Current.Session["currentUserId"] = currentUserId;
if (HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON] != null)
{
//show when there is extra question
skipBtn.Style["visibility"] = "show";
HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON] = null;
}
else
{
//make it hidden when there is no extra question
skipBtn.Style["visibility"] = "hidden";
}
}
else if (questionType == "radiobutton")
{
RadioButtonList radioButtonList = new RadioButtonList();
radioButtonList.ID = "questionRadioButton";
//get options associated with this current question and dump into checkBoxList
SqlCommand optionCommand = new SqlCommand("SELECT * FROM Options WHERE questionId = " + currentQuestionID, connection);
SqlDataReader optionReader = optionCommand.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReader.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReader["optionText"], optionReader["optionId"].ToString());
radioButtonList.Items.Add(item);
}
QuestionPlaceHolder.Controls.Add(radioButtonList);
}
}
}
}
public static DataTable[] Get_PreCut_DetailBfOSCut(string Go, string MO, string CHBfOSCut)
{
DataTable[] tb = new DataTable[5];
string SQL = "";
SQL += " SELECT CUSTOMER_NAME,A.SIZE_CD ,SUM(ACTUAL_QTY) AS ACTUAL_QTY ";
SQL += " FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "', '" + CHBfOSCut + "') A ";
SQL += " inner join (select MAX(D.SHORT_NAME) as CUSTOMER_NAME,SIZE_CD,SEQ ";
SQL += " from CP_MO_HD B WITH(NOLOCK)"; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL += " INNER JOIN SC_HD C WITH(NOLOCK) ON B.GO_NO=C.SC_NO "; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL += " LEFT JOIN GEN_CUSTOMER D WITH(NOLOCK) ON C.CUSTOMER_CD=D.CUSTOMER_CD "; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL += " INNER JOIN CP_SIZE_SEQ S WITH(NOLOCK) "; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL += " on S.MO_NO=B.MO_NO ";
SQL += " where B.GO_NO='" + Go + "' ";
SQL += " AND B.MO_NO LIKE '%" + MO + "%'";
SQL += " group by SIZE_CD,SEQ ) SEQ";
SQL += " on SEQ.SIZE_CD=A.SIZE_CD ";
SQL += " WHERE 1=1";
SQL += " GROUP BY CUSTOMER_NAME,A.SIZE_CD,SEQ.SEQ ";
SQL += " ORDER BY SEQ.SEQ";
tb[0] = DBUtility.GetTable(SQL, "MES");
//TB[1]
//Modified by Jin Song MES133 (Add Color Desc)
SQL = " SELECT X.COLOR_CD,X.COLOR_DESC,Y.ORDER_QTY,X.FAB_PATTERN, ";
SQL = SQL + " (str(ROUND(Y.ADJUST_QTY*100.0/Y.ORDER_QTY,2),10,2)+'%') AS ADJUST_PERCENTS,";
SQL = SQL + " Y.ADJUST_QTY,str(Y.ACTUAL_QTY,10,2),str(X.FABRIC_QTY,10,2),CASE WHEN X.FABRIC_QTY -Y.ACTUAL_QTY/12.0* (Y.MARKER_YPD+X.BINDING_YPD)<0 then 0 ELSE X.FABRIC_QTY -Y.ACTUAL_QTY/12.0* (Y.MARKER_YPD+X.BINDING_YPD) END AS BALANCE_QTY";
SQL = SQL + " ,STR(X.RECEIVED_QTY,10,2)";
SQL = SQL + " ,STR(X.SPARE_FABRIC_QTY,10,2)";
SQL = SQL + " ,STR(X.BINDING_FABRIC_QTY,10,2)";
SQL = SQL + " ,STR(X.RECEIVED_QTY-X.BINDING_FABRIC_QTY-X.SPARE_FABRIC_QTY,10,2)";
SQL = SQL + " ,STR(X.ALLOCATED_QTY,10,2)";
SQL = SQL + " ,STR(X.RECEIVED_QTY-X.BINDING_FABRIC_QTY-X.SPARE_FABRIC_QTY-X.ALLOCATED_QTY,10,2)";
SQL = SQL + " ,str(Y.NET_YPD,10,2),str(Y.MARKER_YPD,10,2),str(X.PPO_YPD,10,2), ";
SQL = SQL + " str(Y.MARKER_UTILIZATION,10,2)+'%' ";
SQL = SQL + " ,str(Y.MARKER_WASTAGE,10,2)+'%',";
SQL = SQL + " X.FABRIC_WIDTH ";
SQL = SQL + " FROM ";
SQL = SQL + " ( ";
SQL = SQL + " SELECT A.COLOR_CD,A.COLOR_DESC,SUM(FABRIC_QTY-ISNULL(SPARE_FABRIC_QTY,0)) AS FABRIC_QTY, ";
SQL = SQL + " SUM(ISNULL(FABRIC_QTY,0)) AS RECEIVED_QTY ";
SQL = SQL + " ,SUM(ISNULL(ALLOCATED_QTY,0)) AS ALLOCATED_QTY,SUM(ISNULL(SPARE_FABRIC_QTY,0)) AS SPARE_FABRIC_QTY";
SQL = SQL + " ,SUM(ISNULL(BINDING_FABRIC_QTY,0)) AS BINDING_FABRIC_QTY,";
SQL = SQL + " MAX(A.PPO_YPD) AS PPO_YPD,MAX(ISNULL(BINDING_YPD,0)) AS BINDING_YPD,MAX(A.WIDTH) AS FABRIC_WIDTH,MAX(A.FAB_PATTERN) AS FAB_PATTERN,MAX(PERCENT_OVER_ALLOWED) AS PERCENT_OVER_ALLOWED ";
SQL = SQL + " FROM CP_FABRIC_ITEM A INNER JOIN SC_HD B ON A.GO_NO=B.SC_NO "; //By ZouShiChang ON 2013.08.29 MES024 SC_HD Change SC_HD
SQL = SQL + " WHERE 1=1";
if (!Go.Equals(""))
{
SQL = SQL + " AND A.GO_NO='" + Go + "' ";
}
SQL = SQL + " AND EXISTS(SELECT 1 FROM CP_MARKER_SET C ";
SQL = SQL + " INNER JOIN CP_MARKER D ON C.MARKER_SET_ID = D.MARKER_SET_ID ";
SQL = SQL + " INNER JOIN CP_MARKER_FABRIC E ON D.MARKER_ID = E.MARKER_ID";
SQL = SQL + " INNER JOIN CP_MO_HD F ON C.MO_NO = F.MO_NO";
SQL = SQL + " WHERE A.FAB_ITEM_ID = E.FAB_ITEM_ID AND A.GO_NO=F.GO_NO AND A.PART_TYPE_CD=F.PART_TYPE";
if (!MO.Equals(""))
{
SQL = SQL + " AND C.MO_NO ='" + MO + "'";
}
SQL = SQL + " )GROUP BY A.COLOR_CD, A.COLOR_DESC ";
SQL = SQL + " ) X ";
SQL = SQL + " INNER JOIN ";
SQL = SQL + " ( ";
SQL = SQL + " SELECT Z.COLOR_CD ,G.ORDER_QTY,G.ADJUST_QTY,G.ACTUAL_QTY ,Z.MARKER_WASTAGE, Z.MARKER_UTILIZATION, Z.NET_YPD ,Z.MARKER_YPD ";
SQL = SQL + " FROM ";
SQL = SQL + " (";
SQL = SQL + " SELECT REF_COLOR AS COLOR_CD,SUM(TOTAL_LENGTH)/SUM(GMT_QTY)*12 AS NET_YPD,SUM(ACT_QTY)/SUM(GMT_QTY)*100 AS MARKER_UTILIZATION,";
SQL = SQL + " SUM(TOTAL_LENGTH*(1+MARKER_WASTAGE*0.01))/SUM(GMT_QTY)*12 AS MARKER_YPD,";
SQL = SQL + " MAX(MARKER_WASTAGE) AS MARKER_WASTAGE";
SQL = SQL + " FROM [FN_GET_GO_COLOR_YPD]('" + Go + "','" + MO + "')";
SQL = SQL + " GROUP BY REF_COLOR";
SQL = SQL + " ) Z ";
SQL = SQL + " INNER JOIN (";
SQL = SQL + " SELECT A.COLOR_CD,SUM(A.ORDER_QTY) AS ORDER_QTY,SUM(ISNULL(A.ACTUAL_QTY,0)-A.ORDER_QTY) AS ADJUST_QTY,";
SQL = SQL + " SUM(ISNULL(ACTUAL_QTY,0)) AS ACTUAL_QTY FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "','" + CHBfOSCut + "') A ";
SQL = SQL + " GROUP BY A.COLOR_CD ";
SQL = SQL + " ) G ON Z.COLOR_CD=G.COLOR_CD ";
SQL = SQL + " )Y ";
SQL = SQL + " ON X.COLOR_CD=Y.COLOR_CD";
tb[1] = DBUtility.GetTable(SQL, "MES");
//tb[2]
//Modified by Jin Song MES133 (Add Color Desc & PPO No)
//Bug Fix by ZK on 2014-09-17 due to double order qty when more than 1 ppo is used
SQL = " SELECT DISTINCT C.jo_no,c.color_cd,F.COLOR_DESC,c.order_qty,D.PERCENT_SHORT ,str(c.adjust_percents,10,2)+'%',c.adjust_qty,c.ACTUAL_QTY,D.SIZE_CD,D.ACTUAL_QTY ";
//Start modification by LIMML ON 20150701 (ADD MARKER YPD AND PPO YPD)
//SQL = SQL + " AS PLAN_CUT_QTY,isnull(E.ORDER_QTY_BY_COL_SIZE,0) as ORDER_QTY_BY_COL_SIZE, F.PPO_NO";
SQL = SQL + " AS PLAN_CUT_QTY,isnull(E.ORDER_QTY_BY_COL_SIZE,0) as ORDER_QTY_BY_COL_SIZE, F.PPO_NO, I.MARKER_YPD, I.PPO_YPD";
//End modification by LIMML ON 20150701 (ADD MARKER YPD AND PPO YPD)
SQL = SQL + " FROM ";
SQL = SQL + " ( ";
SQL = SQL + " SELECT JO_NO,COLOR_CD,SUM(ORDER_QTY) AS ORDER_QTY, ";
SQL = SQL + " ROUND(SUM(ISNULL(ACTUAL_QTY,0)-ORDER_QTY)*100.0/SUM(ORDER_QTY),2) AS ADJUST_PERCENTS ,SUM(ISNULL(ADJUST_QTY,0)) AS ADJUST_QTY, SUM(ACTUAL_QTY) AS ACTUAL_QTY ";
SQL = SQL + " FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "', '" + CHBfOSCut + "')";
SQL = SQL + " GROUP BY JO_NO,COLOR_CD ";
SQL = SQL + " ) C INNER JOIN ";
SQL = SQL + " (SELECT JO_NO,COLOR_CD,SIZE_CD,('+'+str(MAX(PERCENT_OVER_ALLOWED),10,1)+'%/'+'-'+str(MAX(PERCENT_SHORT_ALLOWED),10,1)+'%') AS PERCENT_SHORT,SUM(ACTUAL_QTY) AS ACTUAL_QTY ";
SQL = SQL + " FROM FN_GET_GO_PRE_CUT_DETAIL('" + Go + "','" + MO + "', '" + CHBfOSCut + "') A ";
SQL = SQL + " LEFT JOIN SC_LOT C WITH(NOLOCK) ON A.JO_NO=C.PO_NO WHERE C.SC_NO='" + Go + "' "; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL = SQL + " GROUP BY JO_NO,COLOR_CD,SIZE_CD ";
SQL = SQL + " )D ON C.JO_NO=D.JO_NO AND C.COLOR_CD=D.COLOR_CD ";
SQL = SQL + " LEFT JOIN";
SQL = SQL + " (SELECT A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')' AS SIZE_CODE,SUM(ISNULL(QTY,0)) AS ORDER_QTY_BY_COL_SIZE FROM JO_HD A WITH(NOLOCK)"; //Added WITH(NOLOCK) by Jin Song 7/2014
//新添加Order Qty;
SQL = SQL + " INNER JOIN JO_DT B WITH(NOLOCK) ON A.JO_NO = B.JO_NO "; //Added WITH(NOLOCK) by Jin Song 7/2014
SQL = SQL + " WHERE SC_NO = '" + Go + "'";
SQL = SQL + " GROUP BY A.JO_NO,COLOR_CODE,SIZE_CODE1+'('+SIZE_CODE2+')'";
SQL = SQL + " )E ON C.JO_NO = E.JO_NO AND C.COLOR_CD = E.COLOR_CODE AND D.SIZE_CD = E.SIZE_CODE";
SQL = SQL + " INNER JOIN CP_FABRIC_ITEM F WITH(NOLOCK) ON F.COLOR_CD = C.COLOR_CD AND F.GO_NO = '" + Go + "'"; //Added by Jin Song MES133
//Start modification by LIMML ON 20150701 (ADD MARKER YPD AND PPO YPD)
SQL = SQL + " INNER JOIN (SELECT G.COLOR_CD, G.CURRENTYPD AS MARKER_YPD, H.YPD AS PPO_YPD FROM (SELECT REF_COLOR AS COLOR_CD,";
SQL = SQL + " ROUND(SUM(TOTAL_LENGTH)/SUM(GMT_QTY)*12*(1+MAX(MARKER_WASTAGE)*0.01)+MAX(BINDING_YPD),2) AS CurrentYPD";
SQL = SQL + " FROM FN_GET_MO_COLOR_YPD('" + MO + "') GROUP BY REF_COLOR ) G";
SQL = SQL + " INNER JOIN (SELECT REF_COLOR, YPD FROM FN_GET_GO_COLOR_YPD('" + Go + "','" + MO + "')) H";
SQL = SQL + " ON G.COLOR_CD = H.REF_COLOR)I ON I.COLOR_CD = C.COLOR_CD";
//End modification by LIMML ON 20150701 (ADD MARKER YPD AND PPO YPD)
SQL = SQL + " order by C.jo_no,color_cd";
tb[2] = DBUtility.GetTable(SQL, "MES");
SQL = "SELECT DISTINCT C.SIZE_CD,A.SEQ FROM CP_SIZE_SEQ A,dbo.CP_MO_HD B,CP_MO_CS_BREAKDOWN C WHERE B.GO_NO='" + Go + "'";
SQL += " AND A.MO_NO LIKE '%" + MO + "%' AND A.MO_NO=B.MO_NO AND B.MO_NO=C.MO_NO AND A.SIZE_CD=C.SIZE_CD ORDER BY 2";
tb[3] = DBUtility.GetTable(SQL, "MES");
SQL = " select JOB_ORDER_NO,COLOR,[SIZE]+''+SIZE2 as size,REMARK ";
SQL = SQL + " From dbo.PRD_JO_REMARKS A, JO_HD B ";
SQL = SQL + " where remark_type in('CUTPLAN_PPC','CUTPLAN_CUT') ";
SQL = SQL + " AND A.FACTORY_CD=B.FACTORY_CD ";
SQL = SQL + " AND A.JOB_ORDER_NO=B.JO_NO ";
SQL = SQL + " AND B.SC_NO ='" + Go + "' ";
SQL = SQL + " ORDER BY JOB_ORDER_NO,REMARK_TYPE,COLOR,[SIZE],SIZE2 ";
tb[4] = DBUtility.GetTable(SQL, "MES");
return(tb);
}
public String UserAuthClientV3()
{
StringBuilder ResponseMsg = new StringBuilder();
SPID = Request["SPID"];
AuthenName = Request["AuthenName"];
AuthenType = Request["AuthenType"];
Password = Request["Password"];
PwdType = Request["PwdType"];
Result = ErrorDefinition.IError_Result_UnknowError_Code;
ErrMsg = ErrorDefinition.IError_Result_UnknowError_Msg;
wt = Request["wt"]; // json or xml
#region 数据校验
if (CommonUtility.IsEmpty(SPID))
{
Result = ErrorDefinition.BT_IError_Result_InValidSPID_Code;
ErrMsg = ErrorDefinition.BT_IError_Result_InValidSPID_Msg + "，不能为空";
// 返回错误信息
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "995");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", "SPID不能为空！");
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "995");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", "SPID不能为空！");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
if (CommonUtility.IsEmpty(AuthenName))
{
Result = ErrorDefinition.BT_IError_Result_InValidRealName_Code;
ErrMsg = ErrorDefinition.BT_IError_Result_InValidRealName_Msg + "，不能为空";
// 返回错误信息
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "996");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", "AuthenName不能为空！");
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "996");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", "AuthenName不能为空！");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
if (String.IsNullOrEmpty(AuthenType))
{
AuthenType = "1";
Regex regMobile = new Regex(@"^1[3458]\d{9}$");
Regex regEmail = new Regex(@"^[0-9a-zA-Z_\-\.]*[0-9a-zA-Z_\-]@[0-9a-zA-Z]+\.+[0-9a-zA-Z_\-.]+$");
Regex regCard = new Regex(@"^(\d{9}|\d{16})$");
if (regMobile.IsMatch(AuthenName))
{
AuthenType = "2";
}
if (regEmail.IsMatch(AuthenName))
{
AuthenType = "4";
}
if (regCard.IsMatch(AuthenName))
{
AuthenType = "3";
}
}
if (CommonUtility.IsEmpty(Password))
{
Result = ErrorDefinition.BT_IError_Result_InValidPassword_Code;
ErrMsg = ErrorDefinition.BT_IError_Result_InValidPassword_Msg + "，不能为空";
// 返回错误信息
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "998");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", "Password不能为空！");
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "998");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", "Password不能为空！");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
#endregion
#region 认证
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 15;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.up_Customer_OV3_Interface_UserAuthForClient";
SqlParameter parSPID = new SqlParameter("@SPID", SqlDbType.VarChar, 8);
parSPID.Value = SPID;
cmd.Parameters.Add(parSPID);
SqlParameter parAuthenName = new SqlParameter("@AuthenName", SqlDbType.VarChar, 256);
parAuthenName.Value = AuthenName;
cmd.Parameters.Add(parAuthenName);
SqlParameter parAuthenType = new SqlParameter("@AuthenType", SqlDbType.VarChar, 2);
parAuthenType.Value = AuthenType;
cmd.Parameters.Add(parAuthenType);
SqlParameter parPwd = new SqlParameter("@Pwd", SqlDbType.VarChar, 100);
parPwd.Value = CryptographyUtil.Encrypt(Password);
cmd.Parameters.Add(parPwd);
SqlParameter parPwdType = new SqlParameter("@PwdType", SqlDbType.VarChar, 100);
parPwdType.Value = "1";
cmd.Parameters.Add(parPwdType);
SqlParameter parResult = new SqlParameter("@Result", SqlDbType.Int);
parResult.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parResult);
SqlParameter parErrMsg = new SqlParameter("@ErrMsg ", SqlDbType.VarChar, 256);
parErrMsg.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parErrMsg);
SqlParameter parCustID = new SqlParameter("@CustID ", SqlDbType.VarChar, 16);
parCustID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parCustID);
SqlParameter parUserAccount = new SqlParameter("@UserAccount ", SqlDbType.VarChar, 16);
parUserAccount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parUserAccount);
SqlParameter parCustType = new SqlParameter("@CustType ", SqlDbType.VarChar, 2);
parCustType.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parCustType);
SqlParameter parUProvinceID = new SqlParameter("@UProvinceID ", SqlDbType.VarChar, 2);
parUProvinceID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parUProvinceID);
SqlParameter parSysID = new SqlParameter("@SysID ", SqlDbType.VarChar, 8);
parSysID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parSysID);
SqlParameter parAreaID = new SqlParameter("@AreaID ", SqlDbType.VarChar, 3);
parAreaID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parAreaID);
SqlParameter parOuterID = new SqlParameter("@outerid ", SqlDbType.VarChar, 30);
parOuterID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parOuterID);
SqlParameter parUserName = new SqlParameter("@UserName ", SqlDbType.VarChar, 30);
parUserName.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parUserName);
SqlParameter parRealName = new SqlParameter("@RealName ", SqlDbType.VarChar, 30);
parRealName.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parRealName);
SqlParameter parNickName = new SqlParameter("@NickName ", SqlDbType.VarChar, 30);
parNickName.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parNickName);
SqlParameter parCertificateCode = new SqlParameter("@CertificateCode", SqlDbType.VarChar, 20);
parCertificateCode.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parCertificateCode);
SqlParameter parEmail = new SqlParameter("@Email", SqlDbType.VarChar, 20);
parEmail.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parEmail);
DBUtility.Execute(cmd, DBUtility.BestToneCenterConStr);
Result = int.Parse(parResult.Value.ToString());
ErrMsg = parErrMsg.Value.ToString();
CustID = parCustID.Value.ToString();
UserAccount = parUserAccount.Value.ToString();
CustType = parCustType.Value.ToString();
UProvinceID = parUProvinceID.Value.ToString();
SysID = parSysID.Value.ToString();
AreaID = parAreaID.Value.ToString();
outerid = parOuterID.Value.ToString();
RealName = parRealName.Value.ToString();
UserName = parUserName.Value.ToString();
NickName = parNickName.Value.ToString();
CertificateCode = parCertificateCode.Value.ToString();
Email = parEmail.Value.ToString();
if (Result == 0)
{
ResponseMsg.Length = 0;
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", Result);
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\",", ErrMsg);
ResponseMsg.AppendFormat("\"CustID\":\"{0}\",", CustID);
ResponseMsg.AppendFormat("\"UserAccount\":\"{0}\",", UserAccount);
ResponseMsg.AppendFormat("\"CustType\":\"{0}\",", CustType);
ResponseMsg.AppendFormat("\"UProvinceID\":\"{0}\",", UProvinceID);
ResponseMsg.AppendFormat("\"SysID\":\"{0}\",", SysID);
ResponseMsg.AppendFormat("\"AreaID\":\"{0}\",", AreaID);
ResponseMsg.AppendFormat("\"outerid\":\"{0}\",", outerid);
ResponseMsg.AppendFormat("\"RealName\":\"{0}\",", RealName);
ResponseMsg.AppendFormat("\"UserName\":\"{0}\",", UserName);
ResponseMsg.AppendFormat("\"CertificateCode\":\"{0}\",", CertificateCode);
ResponseMsg.AppendFormat("\"Email\":\"{0}\",", Email);
ResponseMsg.AppendFormat("\"NickName\":\"{0}\"", NickName);
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
return(ResponseMsg.ToString());
}
else
{
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", Result);
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", ErrMsg);
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", Result);
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", ErrMsg);
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
}
}
catch (Exception exp)
{
if ("json".Equals(wt))
{
ResponseMsg.Append("{");
ResponseMsg.AppendFormat("\"errcode\":\"{0}\",", "-2508");
ResponseMsg.AppendFormat("\"errmsg\":\"{0}\"", exp.ToString());
ResponseMsg.Append("}");
}
else
{
ResponseMsg.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
ResponseMsg.Append("<PayPlatRequestParameter>");
ResponseMsg.Append("<PARAMETERS>");
ResponseMsg.AppendFormat("<ErrCode>{0}</ErrCode>", "-2508");
ResponseMsg.AppendFormat("<ErrMsg>{0}</ErrMsg>", exp.ToString());
ResponseMsg.Append("</PARAMETERS>");
ResponseMsg.Append("</PayPlatRequestParameter>");
}
}
finally
{
}
#endregion
return(ResponseMsg.ToString());
}
public string CallParticipantFI([FromBody] string identifier)
{
DBUtility.InsertInserverLog("T3", identifier);
return(identifier);
}
private static string CreateBookISBNDB(string val)
{
string resp = "";
try
{
ISBNCall call = new ISBNCall();
string callResponse = call.MakeRequest("book", val);
JObject book = JObject.Parse(callResponse);
if (book["error"] != null)
{
// there was an error
resp = "X";
}
else
{
// insert new book record and get rid back
string isbn10 = (string)book["data"][0]["isbn10"];
string isbn13 = (string)book["data"][0]["isbn13"];
string isbnDbId = (string)book["data"][0]["book_id"];
// due to issue with db trigger, add the book then query for id
//string instQ = "INSERT INTO Book (ISBN_10,ISBN_13,ISBNDBID) OUTPUT INSERTED.rid VALUES ('" + isbn10 + "','" + isbn13 + "','" + isbnDbId + "')";
//string bookRid = DBUtility.ExecuteScalar(instQ, "Library");
string instQ = "INSERT INTO Book (ISBN_10,ISBN_13,ISBNDBID,ISBNDB_SOURCE) VALUES ('" + isbn10 + "','" + isbn13 + "','" + isbnDbId + "','ISBNdbAPI')";
string instResp = DBUtility.ExecuteSql(instQ, "Library");
// get rid back
string getBookIdWhere = "";
switch (val.Length)
{
case 10:
getBookIdWhere = "WHERE ISBN_10 = '" + val + "'";
break;
case 13:
getBookIdWhere = "WHERE ISBN_13 = '" + val + "'";
break;
default:
break;
}
string getBookIdQ = "SELECT rid FROM SelectViewBook " + getBookIdWhere;
string bookRid = DBUtility.ExecuteScalar(getBookIdQ, "Library");
// now update the book specific values
string bookTitle = (string)book["data"][0]["title"];
string bookSummary = (string)book["data"][0]["summary"];
string bookPubTxt = (string)book["data"][0]["publisher_text"];
string bUpdtQ = "UPDATE Book SET ";
bUpdtQ += "BookTitle = dbo.ProperCase('" + bookTitle.Replace("'", "''") + "'), ";
bUpdtQ += "BookDescription = '" + bookSummary.Replace("'", "''") + "', ";
bUpdtQ += "PublisherText = '" + bookPubTxt.Replace("'", "''") + "' ";
bUpdtQ += "WHERE rid = " + bookRid;
DBUtility.ExecuteSql(bUpdtQ, "Library");
// now check publisher
string isbnDbPubId = (string)book["data"][0]["publisher_id"];
string checkPubQ = "SELECT COUNT(*) FROM Publisher WHERE ISBNDBID = '" + isbnDbPubId + "'";
int checkPub = Int32.Parse(DBUtility.ExecuteScalar(checkPubQ, "Library"));
if (checkPub > 0)
{
// we have the publisher, assign to book
string getPubRid = "SELECT TOP 1 rid FROM Publisher WHERE ISBNDBID = '" + isbnDbPubId + "'";
string oldPubRid = DBUtility.ExecuteScalar(getPubRid, "Library");
bUpdtQ = "UPDATE Book SET Publisher_rid = " + oldPubRid + " WHERE rid = " + bookRid;
DBUtility.ExecuteSql(bUpdtQ, "Library");
}
else
{
// get the publisher from call
string pubResponse = call.MakeRequest("publisher", isbnDbPubId);
JObject pub = JObject.Parse(pubResponse);
string pubName = (string)pub["data"][0]["name"];
string pubLocation = (string)pub["data"][0]["location"];
string instPubQ = "INSERT INTO Publisher (PublisherName,LocationCity,ISBNDBID) OUTPUT INSERTED.rid VALUES ('" + pubName.Replace("'", "''") + "','" + pubLocation.Replace("'", "''") + "','" + isbnDbPubId + "')";
string newPubRid = DBUtility.ExecuteScalar(instPubQ, "Library");
bUpdtQ = "UPDATE Book SET Publisher_rid = " + newPubRid + " WHERE rid = " + bookRid;
DBUtility.ExecuteSql(bUpdtQ, "Library");
}
// now get authors
JArray authors = (JArray)book["data"][0]["author_data"];
for (int i = 0; i < authors.Count; i++)
{
string isbnDbAuthId = authors[i]["id"].ToString();
// check if we have author
string checkAuthorQ = "SELECT COUNT(*) FROM Author WHERE ISBNDBID = '" + isbnDbAuthId + "'";
int checkAuthor = Int32.Parse(DBUtility.ExecuteScalar(checkAuthorQ, "Library"));
if (checkAuthor > 0)
{
// get our rid
string getAuthorRid = "SELECT rid FROM Author WHERE ISBNDBID = '" + isbnDbAuthId + "'";
string oldAuthorRid = DBUtility.ExecuteScalar(getAuthorRid, "Library");
// we have author. assign to book
string bookAuthQ = "INSERT INTO Book_Author (Book_rid, Author_rid) VALUES (" + bookRid + "," + oldAuthorRid + ")";
DBUtility.ExecuteSql(bookAuthQ, "Library");
}
else
{
// we need to create the author
string authorResponse = call.MakeRequest("author", isbnDbAuthId);
JObject auth = JObject.Parse(authorResponse);
string authFName = (string)auth["data"][0]["first_name"];
string authLName = (string)auth["data"][0]["last_name"];
string instAuthQ = "INSERT INTO AUTHOR (FirstName,LastName,ISBNDBID) OUTPUT INSERTED.rid VALUES ('" + authFName.Replace("'", "''") + "','" + authLName.Replace("'", "''") + "','" + isbnDbAuthId + "')";
string newAuthorRid = DBUtility.ExecuteScalar(instAuthQ, "Library");
string bookAuthQ = "INSERT INTO Book_Author (Book_rid, Author_rid) VALUES (" + bookRid + "," + newAuthorRid + ")";
DBUtility.ExecuteSql(bookAuthQ, "Library");
}
}
resp = bookRid;
}
}
catch (Exception e)
{
resp = "X";
}
return(resp);
}
protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = DBUtility.getTareas(DropDownList1.SelectedValue.ToString());
GridView1.DataBind();
}
protected int UserAuthV2(string SPID, string AuthenName, string AuthenType, string Password, HttpContext Context, string ProvinceID, string IsQuery, string PwdType, out string ErrMsg, out string CustID, out string UserAccount, out string CustType, out string outerid, out string ProvinceID1, out string RealName, out string UserName, out string NickName)
{
StringBuilder strLog = new StringBuilder();
strLog.AppendFormat("UserAuthV3-AuthenType:{0},AuthenName:{1},Password:{2}\r\n", AuthenType, AuthenName, Password);
int Result = ErrorDefinition.IError_Result_UnknowError_Code;
ErrMsg = "";
CustID = "";
UserAccount = "";
CustType = "";
RealName = "";
UserName = "";
NickName = "";
outerid = "";
ProvinceID1 = "";
string UProvinceID = "";
string SysID = "";
string AreaID = "";
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 15;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.up_Customer_OV3_Interface_UserAuthV2";
SqlParameter parSPID = new SqlParameter("@SPID", SqlDbType.VarChar, 8);
parSPID.Value = SPID;
cmd.Parameters.Add(parSPID);
SqlParameter parAuthenName = new SqlParameter("@AuthenName", SqlDbType.VarChar, 256);
parAuthenName.Value = AuthenName;
cmd.Parameters.Add(parAuthenName);
SqlParameter parAuthenType = new SqlParameter("@AuthenType", SqlDbType.VarChar, 2);
parAuthenType.Value = AuthenType;
cmd.Parameters.Add(parAuthenType);
SqlParameter parPwd = new SqlParameter("@Pwd", SqlDbType.VarChar, 100);
parPwd.Value = CryptographyUtil.Encrypt(Password);
cmd.Parameters.Add(parPwd);
SqlParameter parPwdType = new SqlParameter("@PwdType", SqlDbType.VarChar, 100);
parPwdType.Value = PwdType;
cmd.Parameters.Add(parPwdType);
SqlParameter parResult = new SqlParameter("@Result", SqlDbType.Int);
parResult.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parResult);
SqlParameter parErrMsg = new SqlParameter("@ErrMsg ", SqlDbType.VarChar, 256);
parErrMsg.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parErrMsg);
SqlParameter parCustID = new SqlParameter("@CustID ", SqlDbType.VarChar, 16);
parCustID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parCustID);
SqlParameter parUserAccount = new SqlParameter("@UserAccount ", SqlDbType.VarChar, 16);
parUserAccount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parUserAccount);
SqlParameter parCustType = new SqlParameter("@CustType ", SqlDbType.VarChar, 2);
parCustType.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parCustType);
SqlParameter parUProvinceID = new SqlParameter("@UProvinceID ", SqlDbType.VarChar, 2);
parUProvinceID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parUProvinceID);
SqlParameter parSysID = new SqlParameter("@SysID ", SqlDbType.VarChar, 8);
parSysID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parSysID);
SqlParameter parAreaID = new SqlParameter("@AreaID ", SqlDbType.VarChar, 3);
parAreaID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parAreaID);
SqlParameter parOuterID = new SqlParameter("@outerid ", SqlDbType.VarChar, 30);
parOuterID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parOuterID);
SqlParameter parUserName = new SqlParameter("@UserName ", SqlDbType.VarChar, 30);
parUserName.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parUserName);
SqlParameter parRealName = new SqlParameter("@RealName ", SqlDbType.VarChar, 30);
parRealName.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parRealName);
SqlParameter parNickName = new SqlParameter("@NickName ", SqlDbType.VarChar, 30);
parNickName.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parNickName);
DBUtility.Execute(cmd, DBUtility.BestToneCenterConStr);
Result = int.Parse(parResult.Value.ToString());
ErrMsg = parErrMsg.Value.ToString();
CustID = parCustID.Value.ToString();
UserAccount = parUserAccount.Value.ToString();
CustType = parCustType.Value.ToString();
UProvinceID = parUProvinceID.Value.ToString();
SysID = parSysID.Value.ToString();
AreaID = parAreaID.Value.ToString();
outerid = parOuterID.Value.ToString();
ProvinceID1 = UProvinceID;
RealName = parRealName.Value.ToString();
UserName = parUserName.Value.ToString();
NickName = parNickName.Value.ToString();
strLog.AppendFormat("UserAuthV3认证结果：Result:{0};ErrMsg:{1};CustID:{2};CustType:{3};UProvinceID:{4};RealName:{5};UserName:{6}", Result, ErrMsg, CustID, CustType, UAProvinceID, RealName, UserName);
}
catch (Exception ex)
{
strLog.AppendFormat(ex.ToString());
}
finally
{
log(strLog.ToString());
}
return(Result);
}
private void Save()//保存新增,編輯或刪除的資料
{
if (m_state == "")
{
return;
}
if (!Valid_Data_H())
{
return;
}
dgvDetails.CloseEditor();
if (!Valid_Data_D())//檢查明細資料的有效性
{
return;
}
if (luevendor_id.Text == "")
{
MessageBox.Show(clsCommon.GetTitle("t_msg_vendor_id_cannot_empty"), myMsg.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
luevendor_id.Focus();
return;
}
bool save_flag = false;
#region 保存新增
if (m_state == "NEW" || m_state == "EDIT")
{
if (m_state == "NEW")
{
string ls_sql = String.Format("Select '1' FROM dbo.po_receipt_goods_temp WHERE id='{0}'", txtID.Text);
if (clsCommon.Valid_Doc(ls_sql)) //檢查主鍵是否存在
{
//重新取最大單據編號
txtID.Text = clsTempReceipt.GetMaxNo();
}
}
const string ls_sql_i =
@"INSERT dbo.po_receipt_goods_temp(id,ir_date,vendor,vendor_id,deliveryman,deliver_id,consignee,remark,iqc_result,iqc_state,
iqc_complete_date,department_id,separate,state,create_date,create_by)
VALUES(@id,@ir_date,@vendor,@vendor_id,@deliveryman,@deliver_id,@consignee,@remark,@iqc_result,@iqc_state,
@iqc_complete_date,@department_id,@separate,@state,getdate(),@user_id)";
const string ls_sql_u =
@"UPDATE dbo.po_receipt_goods_temp
SET separate=@separate,ir_date=@ir_date,vendor=@vendor,vendor_id=@vendor_id,deliveryman=@deliveryman,deliver_id=@deliver_id,
consignee=@consignee,remark=@remark,iqc_result=@iqc_result,iqc_state=@iqc_state,iqc_complete_date=@iqc_complete_date,
department_id=@department_id,state=@state,update_date=getdate(),update_by=@user_id
WHERE id=@id";
SqlConnection myCon = new SqlConnection(DBUtility.ConnectionString);
myCon.Open();
SqlTransaction myTrans = myCon.BeginTransaction();
using (SqlCommand myCommand = new SqlCommand()
{
Connection = myCon, Transaction = myTrans
})
{
try
{
if (m_state == "NEW")
{
myCommand.CommandText = ls_sql_i;
}
else
{
myCommand.CommandText = ls_sql_u;
}
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtID.Text.Trim());
myCommand.Parameters.AddWithValue("@ir_date", detir_date.Text);
myCommand.Parameters.AddWithValue("@vendor_id", luevendor_id.EditValue.ToString());
myCommand.Parameters.AddWithValue("@vendor", txtvendor.Text);
myCommand.Parameters.AddWithValue("@deliveryman", txtdeliveryman.Text);
myCommand.Parameters.AddWithValue("@deliver_id", txtdeliver_id.Text);
myCommand.Parameters.AddWithValue("@consignee", txtconsignee.Text);
myCommand.Parameters.AddWithValue("@remark", txtremark.Text);
myCommand.Parameters.AddWithValue("@iqc_result", "");
myCommand.Parameters.AddWithValue("@iqc_state", "");
myCommand.Parameters.AddWithValue("@iqc_complete_date", DBNull.Value);
myCommand.Parameters.AddWithValue("@department_id", luedepartment_id.EditValue.ToString());
myCommand.Parameters.AddWithValue("@separate", lueseparate.EditValue.ToString());
myCommand.Parameters.AddWithValue("@state", luestate.EditValue.ToString());
myCommand.Parameters.AddWithValue("@user_id", DBUtility._user_id);
myCommand.ExecuteNonQuery();
//處理明細資料
string ls_row_status = "";
if (dgvDetails.RowCount > 0)
{
const string sql_item_i =
@"INSERT INTO dbo.po_receipt_details_temp(
id,sequence_id,mo_id,goods_id,color,po_id,po_ver,po_sequence_id,temp_qty,m_receipt_qty,fact_qty,basic_unit,unit_code,location_id,carton_code,location,
price,m_rate,exchange_rate,total_sum,sec_qty,sec_unit,qc_result,qc_state,currency,s_qty,s_date,remark,sell_order)
VALUES(@id,@sequence_id,@mo_id,@goods_id,@color,@po_id,@po_ver,@po_sequence_id,@temp_qty,@m_receipt_qty,@fact_qty,@basic_unit,@unit_code,
@location_id,@carton_code,@location,@price,@m_rate,@exchange_rate,@total_sum,@sec_qty,@sec_unit,@qc_result,@qc_state,@currency,@s_qty,
CASE WHEN LEN(@s_date)=0 THEN NULL ELSE @s_date END,@remark,@sell_order,@location)";
const string sql_item_u =
@"UPDATE dbo.po_receipt_details_temp
SET mo_id=@mo_id,goods_id=@goods_id,color=@color,po_id=@po_id,po_ver=@po_ver,po_sequence_id=@po_sequence_id,temp_qty=@temp_qty,m_receipt_qty=@m_receipt_qty,
fact_qty=@fact_qty,basic_unit=@basic_unit,unit_code=@unit_code,location_id=@location_id,carton_code=@carton_code,location=@location,
price=@price,m_rate=@m_rate,exchange_rate=@exchange_rate,total_sum=@total_sum,sec_qty=@sec_qty,sec_unit=@sec_unit,qc_result=@qc_result,qc_state=@qc_state,currency=@currency,
s_qty=@s_qty,s_date=CASE WHEN LEN(@s_date)=0 THEN NULL ELSE @s_date END,remark=@remark,sell_order=@sell_order
WHERE id=@id and sequence_id=@sequence_id";
const string sql_item_d = @"DELETE FROM dbo.po_receipt_details_temp WHERE id=@id and sequence_id=@sequence_id";
//處進點擊[項目刪除]操作刪除的記錄
if (m_state == "EDIT")
{
if (dtTempDel.Rows.Count > 0)
{
for (int i = 0; i < dtTempDel.Rows.Count; i++)
{
myCommand.CommandText = sql_item_d;
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtID.Text.Trim());
myCommand.Parameters.AddWithValue("@sequence_id", dtTempDel.Rows[i]["sequence_id"].ToString());
myCommand.ExecuteNonQuery();
}
}
}
//保存明細
string ls_seq_id = "";
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
if (m_state == "NEW")
{
dgvDetails.SetRowCellValue(i, "id", txtID.Text);
}
ls_row_status = dtDetail.Rows[i].RowState.ToString();
if (ls_row_status == "Added" || ls_row_status == "Modified")
{
if (ls_row_status == "Added")
{
myCommand.CommandText = sql_item_i;
ls_seq_id = clsTempReceipt.Get_Details_Seq(txtID.Text.Trim()); //新增狀態重新取最大序號;
dgvDetails.SetRowCellValue(i, "sequence_id", ls_seq_id);
}
else
{
myCommand.CommandText = sql_item_u;
ls_seq_id = dtDetail.Rows[i]["sequence_id"].ToString(); //編輯時序號;
}
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtID.Text.Trim());
myCommand.Parameters.AddWithValue("@sequence_id", ls_seq_id);
myCommand.Parameters.AddWithValue("@mo_id", dtDetail.Rows[i]["mo_id"].ToString());
myCommand.Parameters.AddWithValue("@goods_id", dtDetail.Rows[i]["goods_id"].ToString());
myCommand.Parameters.AddWithValue("@color", dtDetail.Rows[i]["color"].ToString());
myCommand.Parameters.AddWithValue("@po_id", dtDetail.Rows[i]["po_id"].ToString()); //採購單號
myCommand.Parameters.AddWithValue("@po_ver", dtDetail.Rows[i]["po_ver"].ToString()); //採購單版本號
myCommand.Parameters.AddWithValue("@po_sequence_id", dtDetail.Rows[i]["po_sequence_id"].ToString()); //採購單序號
myCommand.Parameters.AddWithValue("@temp_qty", objApp.Return_Float_Value(dtDetail.Rows[i]["temp_qty"].ToString())); //暫收數量
myCommand.Parameters.AddWithValue("@m_receipt_qty", objApp.Return_Float_Value(dtDetail.Rows[i]["m_receipt_qty"].ToString())); //多收數量
myCommand.Parameters.AddWithValue("@fact_qty", objApp.Return_Float_Value(dtDetail.Rows[i]["fact_qty"].ToString())); //實際收貨數量
//myCommand.Parameters.AddWithValue("@already_temp_qty", objApp.Return_Float_Value(dtDetail.Rows[i]["already_temp_qty"].ToString()));
myCommand.Parameters.AddWithValue("@basic_unit", dtDetail.Rows[i]["basic_unit"].ToString());
myCommand.Parameters.AddWithValue("@unit_code", dtDetail.Rows[i]["unit_code"].ToString()); //單位
myCommand.Parameters.AddWithValue("@location_id", dtDetail.Rows[i]["location_id"].ToString()); //倉庫
myCommand.Parameters.AddWithValue("@carton_code", dtDetail.Rows[i]["carton_code"].ToString()); //倉位
myCommand.Parameters.AddWithValue("@location", dtDetail.Rows[i]["location"].ToString());
myCommand.Parameters.AddWithValue("@price", objApp.Return_Float_Value(dtDetail.Rows[i]["price"].ToString())); //單價
myCommand.Parameters.AddWithValue("@currency", dtDetail.Rows[i]["currency"].ToString()); //貨幣
myCommand.Parameters.AddWithValue("@m_rate", objApp.Return_Float_Value(dtDetail.Rows[i]["m_rate"].ToString())); //當時匯率
myCommand.Parameters.AddWithValue("@exchange_rate", objApp.Return_Float_Value(dtDetail.Rows[i]["exchange_rate"].ToString())); //當時匯率
myCommand.Parameters.AddWithValue("@total_sum", objApp.Return_Float_Value(dtDetail.Rows[i]["total_sum"].ToString())); //貨品金額
myCommand.Parameters.AddWithValue("@sec_qty", objApp.Return_Float_Value(dtDetail.Rows[i]["sec_qty"].ToString())); //重量
myCommand.Parameters.AddWithValue("@sec_unit", dtDetail.Rows[i]["sec_unit"].ToString()); //重量單位
myCommand.Parameters.AddWithValue("@qc_result", dtDetail.Rows[i]["qc_result"].ToString()); //QC結果
myCommand.Parameters.AddWithValue("@qc_state", dtDetail.Rows[i]["qc_state"].ToString()); //QC狀態
myCommand.Parameters.AddWithValue("@s_qty", objApp.Return_Float_Value(dtDetail.Rows[i]["s_qty"].ToString())); //訂單數量
myCommand.Parameters.AddWithValue("@s_date", !string.IsNullOrEmpty(dtDetail.Rows[i]["s_date"].ToString()) ? dtDetail.Rows[i]["s_date"].ToString() : ""); //要求送貨日期
myCommand.Parameters.AddWithValue("@remark", dtDetail.Rows[i]["remark"].ToString()); //備註
myCommand.Parameters.AddWithValue("@sell_order", dtDetail.Rows[i]["sell_order"].ToString()); //銷售訂單號
myCommand.ExecuteNonQuery();
}
}
}
myTrans.Commit(); //數據提交
save_flag = true;
}
catch (Exception E)
{
myTrans.Rollback(); //數據回滾
save_flag = false;
throw new Exception(E.Message);
}
finally
{
myCon.Close();
myCon.Dispose();
myTrans.Dispose();
}
}
}
#endregion
objToolbar.Set_Button_Enable_Status(toolStrip1, true);
objToolbar.SetToolBar();
lueseparate.Properties.ReadOnly = false;
lueseparate.BackColor = System.Drawing.Color.White;
SetObjValue.SetEditBackColor(this.Controls, false);
Set_Grid_Status(false);
m_state = "";
dtTempDel.Clear();
if (save_flag)
{
Find_Doc();
DBUtility.myMessageBox(myMsg.msgSave_ok, myMsg.msgTitle);
m_id = txtID.Text;
}
else
{
MessageBox.Show(myMsg.msgSave_error, myMsg.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
public long SaveUserProfile(Hashtable userProfileCriteria)
{
long result = 0;
DBUtility objUtility = new DBUtility();
_cmd = new SqlCommand();
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = "GP_SP_InsertUserProfile";
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Name"])))
{
_cmd.Parameters.AddWithValue("@Name", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Name", Convert.ToString(userProfileCriteria["Name"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Email"])))
{
_cmd.Parameters.AddWithValue("@Email", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Email", Convert.ToString(userProfileCriteria["Email"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Address"])))
{
_cmd.Parameters.AddWithValue("@Address", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Address", Convert.ToString(userProfileCriteria["Address"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["CompanyName"])))
{
_cmd.Parameters.AddWithValue("@CompanyName", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@CompanyName", Convert.ToString(userProfileCriteria["CompanyName"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Password"])))
{
_cmd.Parameters.AddWithValue("@Password", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Password", Convert.ToString(userProfileCriteria["Password"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Gender"])))
{
_cmd.Parameters.AddWithValue("@Gender", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Gender", Convert.ToString(userProfileCriteria["Gender"]).Trim());
}
_cmd.Parameters.AddWithValue("@RoleId", Convert.ToString(userProfileCriteria["RoleId"]));
result = objUtility.ExecuteScalar(_cmd);
return(result);
}
public static DataTable GetJobOrderGarmentType(string JoNo)
{
string SQL = " SELECT GARMENT_TYPE_CD FROM JO_HD WHERE JO_NO='" + JoNo + "'";
return(DBUtility.GetTable(SQL, "MES"));
}
public long UpdateUserProfile(Hashtable userProfileCriteria)
{
long result = 0;
DBUtility objUtility = new DBUtility();
_cmd = new SqlCommand();
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = "GP_SP_UpdateUserProfile";
_cmd.Parameters.AddWithValue("@ID", Convert.ToInt64(userProfileCriteria["ID"]));
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Name"])))
{
_cmd.Parameters.AddWithValue("@Name", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Name", Convert.ToString(userProfileCriteria["Name"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Address"])))
{
_cmd.Parameters.AddWithValue("@Address", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Address", Convert.ToString(userProfileCriteria["Address"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["City"])))
{
_cmd.Parameters.AddWithValue("@City", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@City", Convert.ToString(userProfileCriteria["City"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["StateName"])))
{
_cmd.Parameters.AddWithValue("@StateName", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@StateName", Convert.ToString(userProfileCriteria["StateName"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["ZipCode"])))
{
_cmd.Parameters.AddWithValue("@ZipCode", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@ZipCode", Convert.ToInt32(userProfileCriteria["ZipCode"]));
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["CompanyName"])))
{
_cmd.Parameters.AddWithValue("@CompanyName", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@CompanyName", Convert.ToString(userProfileCriteria["CompanyName"]).Trim());
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["LicenceNumber"])))
{
_cmd.Parameters.AddWithValue("@LicenceNumber", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@LicenceNumber", Convert.ToString(userProfileCriteria["LicenceNumber"]));
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["PhoneNumber"])))
{
_cmd.Parameters.AddWithValue("@PhoneNumber", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@PhoneNumber", Convert.ToString(userProfileCriteria["PhoneNumber"]));
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["Password"])))
{
_cmd.Parameters.AddWithValue("@Password", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@Password", Convert.ToString(userProfileCriteria["Password"]));
}
if (string.IsNullOrWhiteSpace(Convert.ToString(userProfileCriteria["IsEnabled"])))
{
_cmd.Parameters.AddWithValue("@IsEnabled", DBNull.Value);
}
else
{
_cmd.Parameters.AddWithValue("@IsEnabled", Convert.ToBoolean(userProfileCriteria["IsEnabled"]));
}
_cmd.Parameters.AddWithValue("@UserID", Convert.ToInt64(userProfileCriteria["UserID"]));
result = objUtility.ExecuteScalar(_cmd);
return(result);
}
public static DataTable GetJobOrderDetailResultList(string JoNo, string factoryCd, string garmentType, string processCd, string processType, string prodFactory, bool confirm, bool draft, string fromDate, string endDate)
{
DateTime d;
string SQL = " select a.DOC_NO,a.TRX_DATE,";
SQL = SQL + " CASE WHEN confirm = 'Y' THEN c.CREATE_DATE ELSE A.CREATE_DATE END AS CREATE_DATE";
SQL = SQL + " ,ISNULL(C.CREATE_USER_ID,D.CREATE_USER_ID) AS CREATE_USER_ID,CONVERT(NVARCHAR(100),isnull(c.SUBMIT_DATE,D.SUBMIT_DATE),120) AS SUBMIT_DATE,ISNULL(C.SUBMIT_USER_ID,D.SUBMIT_USER_ID) AS SUBMIT_USER_ID,A.PROCESS_TYPE,a.PROCESS_CD,a.QTY,a.CONFIRM,b.* ";
SQL = SQL + " from( ";
SQL = SQL + " select DOC_NO,dbo.DATE_FORMAT(TRX_DATE,'yyyy-mm-dd') TRX_DATE,MAX(CONVERT(NVARCHAR(100),CREATE_DATE,120)) AS CREATE_DATE, PROCESS_CD,PROCESS_GARMENT_TYPE,PROCESS_TYPE,Process_PRODUCTION_FACTORY,sum(ISNULL(complete_qty,0)) qty,'Y' confirm ";
SQL = SQL + " from prd_jo_output_trx WITH(NOLOCK) WHERE 1=2 GROUP BY DOC_NO,TRX_DATE,PROCESS_CD,PROCESS_GARMENT_TYPE,PROCESS_TYPE,Process_PRODUCTION_FACTORY ";
if (confirm)
{
SQL = SQL + " union all select DOC_NO,dbo.DATE_FORMAT(TRX_DATE,'yyyy-mm-dd') TRX_DATE,MAX(CONVERT(NVARCHAR(100),CREATE_DATE,120)) AS CREATE_DATE, PROCESS_CD,PROCESS_GARMENT_TYPE,PROCESS_TYPE,Process_PRODUCTION_FACTORY,sum(ISNULL(complete_qty,0)) qty,'Y' confirm ";
SQL = SQL + " from prd_jo_output_trx WITH(NOLOCK)";
SQL = SQL + " WHERE FACTORY_CD='" + factoryCd + "' ";
if (!JoNo.Equals(""))
{
SQL = SQL + " AND JOB_ORDER_NO = '" + JoNo + "' ";
}
if (DateTime.TryParse(fromDate, out d))
{
SQL = SQL + " AND TRX_DATE>='" + fromDate + "'";
}
if (DateTime.TryParse(endDate, out d))
{
SQL = SQL + " AND TRX_DATE<='" + endDate + "'";
}
SQL = SQL + " GROUP BY DOC_NO,TRX_DATE,PROCESS_CD,PROCESS_GARMENT_TYPE,PROCESS_TYPE,Process_PRODUCTION_FACTORY ";
}
if (draft)
{
SQL = SQL + " union all ";
SQL = SQL + " select DOC_NO,dbo.DATE_FORMAT(TRX_DATE,'yyyy-mm-dd')TRX_DATE,NULL AS CREATE_DATE,PROCESS_CD,PROCESS_GARMENT_TYPE,PROCESS_TYPE,Process_PRODUCTION_FACTORY,sum(ISNULL(complete_qty,0)) qty,'N' confirm ";
SQL = SQL + " from prd_jo_output_dft OD WITH(NOLOCK)";
SQL = SQL + " WHERE FACTORY_CD='" + factoryCd + "' ";
if (!JoNo.Equals(""))
{
SQL = SQL + " AND JOB_ORDER_NO = '" + JoNo + "' ";
}
if (DateTime.TryParse(fromDate, out d))
{
SQL = SQL + " AND TRX_DATE>='" + fromDate + "'";
}
if (DateTime.TryParse(endDate, out d))
{
SQL = SQL + " AND TRX_DATE<='" + endDate + "'";
}
//因为有自动生成的单,所以需要过滤prd_jo_output_dft数据;
//按照流程,confirm的时候,PRD_GARMENT_TRANSFER_DFT -(copy)->prd_jo_output_dft-(remove)->prd_jo_output_trx,也就是说,
//prd_jo_output_dft不应该存在PRD_GARMENT_TRANSFER_DFT的DOC;
SQL = SQL + " AND NOT EXISTS (SELECT 1 FROM PRD_GARMENT_TRANSFER_DFT WITH(NOLOCK) WHERE DOC_NO = OD.DOC_NO ";
if (!JoNo.Equals(""))
{
SQL = SQL + " AND JOB_ORDER_NO ='" + JoNo + "'";
}
SQL = SQL + " )";
SQL = SQL + " GROUP BY DOC_NO,TRX_DATE,PROCESS_CD,PROCESS_GARMENT_TYPE,PROCESS_TYPE,Process_PRODUCTION_FACTORY ";
//新添加GTN的DFT数据;
SQL = SQL + " union all ";
SQL = SQL + " SELECT B.DOC_NO,NULL AS TRX_DATE,B.CREATE_DATE,B.PROCESS_CD,B.PROCESS_GARMENT_TYPE,B.PROCESS_TYPE,Process_PRODUCTION_FACTORY,SUM(ISNULL(A.QTY,0)) AS QTY,'N' confirm ";
SQL = SQL + " FROM dbo.PRD_GARMENT_TRANSFER_DFT A WITH(NOLOCK)";
SQL = SQL + " INNER JOIN ";
SQL = SQL + " DBO.PRD_GARMENT_TRANSFER_HD B WITH(NOLOCK) ON A.DOC_NO =B.DOC_NO AND B.FACTORY_CD = '" + factoryCd + "'";
SQL = SQL + " AND B.STATUS = 'N'";
if (!JoNo.Equals(""))
{
SQL = SQL + " AND JOB_ORDER_NO = '" + JoNo + "' ";
}
SQL = SQL + " GROUP BY B.DOC_NO,B.PROCESS_CD,B.PROCESS_GARMENT_TYPE,B.PROCESS_TYPE,B.CREATE_DATE,Process_PRODUCTION_FACTORY";
}
SQL = SQL + " ) a left join prd_jo_output_HD C ON A.DOC_NO=C.DOC_NO LEFT JOIN prd_GARMENT_TRANSFER_HD D ON A.DOC_NO=D.DOC_NO , ";
SQL = SQL + " (select isnull(DISPLAY_SEQ,0) AS SEQ,isNull(DISPLAY_SEQ,0) AS DISPLAY_SEQ,SHORT_NAME,PRC_CD,NM,GARMENT_TYPE,PROCESS_TYPE from gen_prc_cd_mst WITH(NOLOCK) ";//Added By ZouShiChang ON 2013.08.29 MES024 (PROCESS_TYPE)
SQL = SQL + " where factory_cd='" + factoryCd + "' and (isnull(GARMENT_TYPE,'')='' OR GARMENT_TYPE='" + garmentType + "' ) )b ";
SQL = SQL + " where a.process_cd=b.prc_cd ";
SQL = SQL + " AND A.process_garment_type=b.garment_type ";
if (!processCd.Equals("ALL"))
{
SQL = SQL + " and a.process_cd='" + processCd + "' ";
}
if (!garmentType.Equals("ALL"))
{
SQL = SQL + " and a.process_garment_type='" + garmentType + "' ";
}
if (!prodFactory.Equals(""))
{
SQL = SQL + " and a.process_production_factory='" + prodFactory + "' ";
}
SQL = SQL + " order by DOC_NO ";
return(DBUtility.GetTable(SQL, "MES"));
}
public static void SP_Pro_DropTmpTable(string uuidContent, string uuidTitle)
{
string SQL = "exec SP_Pro_DropTmpTable '" + uuidContent + "','" + uuidTitle + "' ";
DBUtility.ExecuteNonQuery(SQL, "MES");
}
protected void nextBtn_Click(object sender, EventArgs e)
{
int currentQuestionID = getCurrentQuestionIDFromSession();
//get extra questions list from session if it exist, if not make a new one
List <int> extraQuestions = new List <int>();
if (HttpContext.Current.Session[SESSION_EXTRA_QUESTION] != null)
{
extraQuestions = (List <int>)HttpContext.Current.Session[SESSION_EXTRA_QUESTION];
}
try
{
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
//check if it was a textbox question
TextBox questionTextBox = (TextBox)QuestionPlaceHolder.FindControl("questionTextBox");
if (questionTextBox != null)
{
//if it was a textBox, do something with the answers
string typedAnswer = questionTextBox.Text;
HttpContext.Current.Session[SESSION_ANSWER_TEXTBOX] = typedAnswer;
//TODO FOR STEVEN
//get hold of optionId, the answerText and userID and add to session
try
{
Answers answer = new Answers();
answer.optionId = null;
answer.answerText = typedAnswer;
///get list from session
List <Answers> answers = GetListOfAnswersFromSession();
answers.Add(answer);
//save this list into the session (overwrite existing list if any)
HttpContext.Current.Session["answers"] = answers;
}
catch (ArgumentException argEx)
{
Response.Write(argEx.Message);
}
catch (FormatException formatEx)
{
Response.Write(formatEx.Message);
}
catch (OverflowException overflowEx)
{
Response.Write(overflowEx.Message);
}
}
//check if it was a checkbox question
CheckBoxList questionCheckBoxList = (CheckBoxList)QuestionPlaceHolder.FindControl("questionCheckBox");
if (questionCheckBoxList != null)
{
List <int> listOptionId = new List <int>();
foreach (ListItem item in questionCheckBoxList.Items)
{
if (item.Selected)
{
try
{
Answers answer = new Answers();
int optionId = int.Parse(item.Value);// may throw exception.
listOptionId.Add(optionId);
SqlCommand optionsCommand = new SqlCommand("SELECT nextQuestionId FROM Options WHERE optionId = " + optionId, connection);
//RUN command and execute straight away , execute scalar gives back the first row and first value in the first column
var dbResult = optionsCommand.ExecuteScalar();
if (dbResult.ToString() != "")
{
extraQuestions.Add((int)dbResult);
extraQuestionAndSkipButton.Add((int)dbResult);
}
//get hold of optionId, the answerText and userID and add to session
try
{
answer.optionId = optionId;
answer.answerText = item.ToString();
///get list from session
List <Answers> answers = GetListOfAnswersFromSession();
answers.Add(answer);
//save this list into the session (overwrite existing list if any)
HttpContext.Current.Session["answers"] = answers;
}
catch (ArgumentException argEx)
{
Response.Write(argEx.Message);
}
catch (FormatException formatEx)
{
Response.Write(formatEx.Message);
}
catch (OverflowException overflowEx)
{
Response.Write(overflowEx.Message);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
}
HttpContext.Current.Session[SESSION_ANSWER_CHECKBOX] = listOptionId;
Debug.WriteLine(HttpContext.Current.Session[SESSION_ANSWER_CHECKBOX]);
Debug.WriteLine(listOptionId);
}
//check if it was a radiobutton question
RadioButtonList questionRadioButtonList = (RadioButtonList)QuestionPlaceHolder.FindControl("questionRadioButton");
if (questionRadioButtonList != null)
{
string selectedAnswer = questionRadioButtonList.SelectedItem.Text;
HttpContext.Current.Session[SESSION_ANSWER_TEXTBOX] = selectedAnswer;
try
{
int optionId = int.Parse(questionRadioButtonList.SelectedValue);// may throw exception.
SqlCommand optionsCommand = new SqlCommand("SELECT nextQuestionId FROM Options WHERE optionId = " + optionId, connection);
//RUN command and execute straight away , execute scalar gives back the first row and first value in the first column
var dbResult = optionsCommand.ExecuteScalar();
if (dbResult.ToString() != "")
{
extraQuestions.Add((int)dbResult);
}
//TODO FOR STEVEN
//get hold of optionId, the answerText and userID and add to session
try
{
Answers answer = new Answers();
answer.optionId = optionId;
answer.answerText = selectedAnswer;
///get list from session
List <Answers> answers = GetListOfAnswersFromSession();
answers.Add(answer);
//save this list into the session (overwrite existing list if any)
HttpContext.Current.Session["answers"] = answers;
}
catch (ArgumentException argEx)
{
Response.Write(argEx.Message);
}
catch (FormatException formatEx)
{
Response.Write(formatEx.Message);
}
catch (OverflowException overflowEx)
{
Response.Write(overflowEx.Message);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
if (extraQuestions.Count <= 0)
{
SqlCommand command = new SqlCommand("SELECT * FROM Questions WHERE questionId = " + currentQuestionID, connection);
//RUN command and dump results into reader
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
//get index for the nextQuestion column
int nextQuestionColumnIndex = reader.GetOrdinal("nextQuestion");
//check if value in this row and column is NULL
if (reader.IsDBNull(nextQuestionColumnIndex))
{
//insert User details and get userId
Users users = (Users)HttpContext.Current.Session["currentUser"];
SqlCommand commandInsertUsers = new SqlCommand("INSERT INTO Users (firstName, lastName, dob, phoneNumber, date, anonymous, ipAddress) VALUES (@firstName, @lastName, @dob, @phoneNumber, @date, @anonymous, @ipAddress); SELECT CAST(scope_identity() as int);", connection);
//add parameter
//prevents sql injection
commandInsertUsers.Parameters.Add("@firstName", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@firstName"].Value = users.firstName;
commandInsertUsers.Parameters.Add("@lastName", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@lastName"].Value = users.lastName;
commandInsertUsers.Parameters.Add("@dob", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@dob"].Value = users.dob;
commandInsertUsers.Parameters.Add("@phoneNumber", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@phoneNumber"].Value = users.phoneNumber;
commandInsertUsers.Parameters.Add("@anonymous", SqlDbType.Int, 4);
commandInsertUsers.Parameters["@anonymous"].Value = users.anon;
commandInsertUsers.Parameters.Add("@date", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@date"].Value = users.date;
commandInsertUsers.Parameters.Add("@ipAddress", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@ipAddress"].Value = users.ipAddress;
//get the userId from database
int newUserId = (int)commandInsertUsers.ExecuteScalar();
Console.WriteLine("New Product Id: " + newUserId);
//Insert Answers
List <Answers> answers = GetListOfAnswersFromSession();
foreach (Answers answer in answers)
{
SqlCommand commandInsert = new SqlCommand("INSERT INTO Answers (optionId, answerText, userId) VALUES (@optionId, @answerText, @userId);", connection);
//add parameter
//prevents sql injection
commandInsert.Parameters.Add("@optionId", SqlDbType.VarChar, 50);
commandInsert.Parameters["@optionId"].Value = answer.optionId;
if (commandInsert.Parameters["@optionId"].Value == null)
{
commandInsert.Parameters["@optionId"].Value = DBNull.Value;
}
commandInsert.Parameters.Add("@answerText", SqlDbType.VarChar, 50);
commandInsert.Parameters["@answerText"].Value = answer.answerText;
commandInsert.Parameters.Add("@userId", SqlDbType.Int, 4);
commandInsert.Parameters["@userId"].Value = newUserId;
var rowsAffected = commandInsert.ExecuteNonQuery();
if (rowsAffected <= 0)
{
//could not insert
//do something about it like show to user that the stuff didnt insert properly
Console.WriteLine("failed to write");
}
}
//empty products out of session
HttpContext.Current.Session["answers"] = null;
//clear all session
Session.Clear();
//if null, at end of survey
Response.Redirect("ThankYouPage.aspx");
}
else
{
//If not null, get the value of the nextQuestion column so we can load that question up next
int nextQuestionId = (int)reader["nextQuestion"];
//save this as the current questionId in session.
HttpContext.Current.Session["questionID"] = nextQuestionId;
HttpContext.Current.Session["currentUserId"] = currentUserId;
//reload this page
Response.Redirect("SurveyQuestions.aspx");
}
}
}
else
{
//if we do have questions on that list
//set current question to load to be equal to first question in the extraQuestions List
HttpContext.Current.Session[QUESTION_ID] = extraQuestions[0];
//add to skip button session
HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON] = extraQuestions[0];
//remove this question from the list
extraQuestions.RemoveAt(0);
//save extraQuestionlist into session
HttpContext.Current.Session[SESSION_EXTRA_QUESTION] = extraQuestions;
HttpContext.Current.Session["currentUserId"] = currentUserId;
//reload this page
Response.Redirect("SurveyQuestions.aspx");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private void Save() //保存新增或修改的資料
{
if (dgvDetails.Rows.Count == 0)
{
return;
}
if (mState == "NEW")
{
if (Valid_Doc())
{
return;
}
}
//dgvDetails.CurrentCell.RowIndex;行號
//Select a Cell Focus
dgvDetails.CurrentCell = dgvDetails.Rows[0].Cells["id"];
//Selected a Row
dgvDetails.Rows[0].Selected = true;
bool flag_datavalid = false;
for (int i = 0; i < dgvDetails.Rows.Count; i++)
{
if (dgvDetails.Rows[i].Cells["id"].Value.ToString() == "" || dgvDetails.Rows[i].Cells["name"].Value.ToString() == "" || dgvDetails.Rows[i].Cells["sort_id"].Value.ToString() == "")
{
flag_datavalid = true;
dgvDetails.CurrentCell = dgvDetails.Rows[i].Cells["id"];//選中當前空白的行
break;
}
}
if (flag_datavalid)
{
MessageBox.Show("vendor id,name or vendor. group id cannot be empty !", myMsg.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
try
{
bds1.EndEdit();
SqlCommandBuilder SCB = new SqlCommandBuilder(SDA);
SDA.InsertCommand = SCB.GetInsertCommand();
SDA.UpdateCommand = SCB.GetUpdateCommand();
SDA.Update(dtDetail);
DBUtility.myMessageBox(myMsg.msgSave_ok, myMsg.msgTitle);
//MessageBox.Show(myMsg.msgSave_ok, myMsg.msgTitle);
SCB = null;
SetButtonSatus(true);
SetObjValue.SetEditBackColor(panel1.Controls, false);
mState = "";
txtid.Properties.ReadOnly = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void iniDbu()
{
dbu = new DBUtility("0", "0", "ODS", "users", "users");
}
