public Customer readFromDatabase(int customerID)
{
DBLink.openConnection();
string sqlUser = "******" + customerID + "';";
MySqlDataReader customerData = DBLink.executeReadQuarry(sqlUser);
if (customerData.Read())
{
Customer existingCustomer = new Customer();
existingCustomer.CustomerID = customerData.GetInt32(0);
existingCustomer.Name = customerData.GetString(1);
existingCustomer.ShortName = customerData.GetString(2);
existingCustomer.Address = customerData.GetString(3);
existingCustomer.Email = customerData.GetString(4);
existingCustomer.PhoneNo = customerData.GetString(5);
existingCustomer.AccountManager = customerData.GetString(6);
return(existingCustomer);
}
return(null);
}
/// <summary>
/// 用户验证【1-成功；0-失败（账号密码不存在或者不匹配）】
/// </summary>
/// <param name="UserName"></param>
/// <param name="PassWrod"></param>
/// <returns></returns>
public int Authentication(string Account, string Pwd)
{
SqlConnection conn = DBLink.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "AdminAuthentication";
cmd.Parameters.Add(new SqlParameter("@AdminAccount", Account));
cmd.Parameters.Add(new SqlParameter("@AdminPwd", Pwd));
try
{
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt != null)
{
return(1);
}
else
{
return(0);
}
}
catch (Exception)
{
return(0);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
public string GetDataWZ()
{
DBLink dl = new DBLink();
string errMsg = "";
string sql = "select * from t_info_graphic where t_graphicid ='2'";
// string sql ="select g.t_controlid,g.t_tag from t_info_histvalue h "+
// "inner join t_info_graphic g on h.t_tag =g.t_tag "+
//"and g.t_graphicid ='1' group by g.t_controlid,g.t_tag";
DataTable dtt = new DataTable();//select * from t_info_graphic where t_graphicid ='1' order by t_controlid asc
DataTable dt = dl.RunDataTable(sql, out errMsg);
string str = "";
for (int i = 0; i < dt.Rows.Count; i++)
{//select * from (select * from t_info_histvalue where T_TAG='GHPV_1_000WG001' order by t_time desc) where ROWNUM =1 order by ROWNUM asc
string sql_value = "select * from (select * from t_info_histvalue where T_TAG='" + dt.Rows[i]["t_tag"].ToString() + "' order by t_time desc) where ROWNUM =1 order by ROWNUM asc";
DateTime dt1 = DateTime.Now;
dtt = dl.RunDataTable(sql_value, out errMsg);
DateTime dt2 = DateTime.Now;
double num = (dt2 - dt1).TotalSeconds;
if ((dtt.Rows.Count > 0) && (dtt.Rows[0]["D_VALUE"].ToString() != ""))
{
str += dt.Rows[i]["t_controlid"].ToString() + "," + Convert.ToDouble(dtt.Rows[0]["D_VALUE"].ToString()).ToString("f2") + ";";
}
else
{
str += dt.Rows[i]["t_controlid"].ToString() + ",0;";
}
}
return(str);
}
public static bool InsertNew(PW_GROUP t_new)
{
DBHelper DBHelper_SqlServer = new DBLink();
try
{
PW_GROUP tt = t_new;
StringBuilder sb = new StringBuilder();
sb.Append(" Insert into PW_GROUP ( ");
sb.Append("ID, ");
sb.Append("GROUPNAME, ");
sb.Append("GROUPDESCRIBE, ");
sb.Append("STATUS )");
sb.Append(" values ( ");
sb.Append("@ID,");
sb.Append("@GROUPNAME,");
sb.Append("@GROUPDESCRIBE,");
sb.Append(" @STATUS )");
DbCommand cmd = new SqlCommand();
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@ID", DbType.String)
{
Value = tt.ID
});
cmd.Parameters.Add(new SqlParameter("@GROUPNAME", DbType.String)
{
Value = tt.GROUPNAME
});
cmd.Parameters.Add(new SqlParameter("@GROUPDESCRIBE", DbType.String)
{
Value = tt.GROUPDESCRIBE
});
cmd.Parameters.Add(new SqlParameter("@STATUS", DbType.String)
{
Value = tt.STATUS
});
foreach (SqlParameter p in cmd.Parameters)
{
p.IsNullable = true;
if (p.Value == null)
{
p.Value = DBNull.Value;
}
}
int val = DBHelper_SqlServer.ExecuteNonQuery(cmd);
if (val > 0)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
public string GetData(string id)
{
BLL.BLLDefault bll_df = new BLL.BLLDefault();
DBLink dl = new DBLink();
string errMsg = "";
string sql = "select * from t_info_graphic where t_graphicid ='" + id + "'";
DataTable dtt = new DataTable();//select * from t_info_graphic where t_graphicid ='1' order by t_controlid asc
DataTable dt = dl.RunDataTable(sql, out errMsg);
string str = "";
for (int i = 0; i < dt.Rows.Count; i++)
{//select * from (select * from t_info_histvalue where T_TAG='GHPV_1_000WG001' order by t_time desc) where ROWNUM =1 order by ROWNUM asc
// string sql_value = "select * from (select * from t_info_histvalue where T_TAG='" + dt.Rows[i]["t_tag"].ToString() + "' order by t_time desc) where ROWNUM =1 order by ROWNUM asc";
dtt = bll_df.GetDT(dt.Rows[i]["t_tag"].ToString(), DateTime.Now);
if ((dtt.Rows.Count > 0) && (dtt.Rows[0]["D_VALUE"].ToString() != ""))
{
str += dt.Rows[i]["t_controlid"].ToString() + "," + Convert.ToDouble(dtt.Rows[0]["D_VALUE"].ToString()).ToString("f2") + ";";
}
else
{
str += dt.Rows[i]["t_controlid"].ToString() + ",0;";
}
}
return(str);
}
/// <summary>
/// 用户验证【1-成功；0-失败（账号密码不存在或者不匹配）】
/// </summary>
/// <param name="UserName"></param>
/// <param name="PassWrod"></param>
/// <returns></returns>
public int Authentication(string EmployerAccount, string EmployerPwd)
{
int count = 0;
SqlConnection conn = DBLink.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "EmployerAuthentication";
cmd.Parameters.Add(new SqlParameter("@EmployerAccount", EmployerAccount));
cmd.Parameters.Add(new SqlParameter("@EmployerPwd", EmployerPwd));
try
{
conn.Open();
count = int.Parse(cmd.ExecuteScalar().ToString());
if (count > 0)
{
return(1);
}
else
{
return(0);
}
}
catch (Exception)
{
return(0);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 取得網站成績
/// </summary>
/// <param name="pData"></param>
/// <returns></returns>
public JsonResult getWebTestResult(EGG_TEST_INFO pData)
{
RESPONSE_MSG rm = new RESPONSE_MSG();
string actionName = "getWebTestResult";
DBLink link = new DBLink();
bool hasUpdate = false;
JsonResult jr = null;
string LineID = DateTime.Now.ToString("yyyyMMddHHmmssfff");
try
{
if (this.AnserList.Skip(5).Take(3).Contains(pData.BUY_PLACE))
{
pData.TEMP = this.getNowTemp(pData.BUY_PLACE);
}
this.insertRowData(ref pData, ref rm, ref link, LineID);
pData.DAYS = ((DateTime.Now - DateTime.Parse(pData.BUY_DATE)).Days + 1).ToString();
hasUpdate = true;
this.updateRowData(ref pData, ref hasUpdate, ref rm, ref link, LineID, "");
jr = this.getTestResult(LineID);
}
catch (Exception ex)
{
rm.status = RESPONSE_STATUS.ERROR;
rm.message = "程式碼發生錯誤";
LogTool.Fatal(ex, actionName, this.csName);
}
return(Json(jr.Data, JsonRequestBehavior.AllowGet));
}
public JsonResult AddTable(string Table, string Info)
{
using (var db = DBLink.CreateDB())
try
{
string GUID = Guid.NewGuid().ToString();
var table = JsonConvert.DeserializeObject <T_Table>(Table);
table.GUID = GUID;
var LInfo = JsonConvert.DeserializeObject <List <T_Table_Info> >(Info);
foreach (var item in LInfo)
{
item.Table_GUID = GUID;
}
db.Ado.BeginTran();
db.Insertable <T_Table>(table).ExecuteCommand();
db.Insertable <T_Table_Info>(LInfo).ExecuteCommand();
db.Ado.CommitTran();
return(Json(new { status = 1, data = "", msg = "" }));
}
catch (Exception ex)
{
return(Json(new { status = 0, data = "", msg = ex.Message }));
}
}
public User readFromDatabase(String userName)
{
string sqlUser = "******" + userName + "';";
DBLink.openConnection();
MySqlDataReader userData = DBLink.executeReadQuarry(sqlUser);
if (userData.Read())
{
User existingUser = FactoryUser.getUserObj((Security.Permissions)userData.GetInt32(2));
existingUser.UserName = userData.GetString(0);
existingUser.Name = userData.GetString(1);
existingUser.Permissions = userData.GetInt32(2);
existingUser.UserRating = userData.GetString(3);
existingUser.MonthlyRevnue = userData.GetDecimal(4);
existingUser.Predecessor = userData.GetString(5);
existingUser.Notes = userData.GetString(6);
return(existingUser);
}
return(null);
}
private static List <Query_Stored> DoQuery(string sql_str)
{
List <Query_Stored> rtn = new List <Query_Stored>();
try
{
DBHelper DBHelper_SqlServer = new DBLink();
using (IDataReader dr = DBHelper_SqlServer.ExecuteReader(sql_str))
{
while (dr.Read())
{
var p = new Query_Stored();
#region 逐个赋值
p.WAREHOUSENO = DBHelper_SqlServer.GetDataValue <string>(dr, "WAREHOUSENO");
p.PLACEID = DBHelper_SqlServer.GetDataValue <string>(dr, "PLACEID");
p.CONTAINERID = DBHelper_SqlServer.GetDataValue <string>(dr, "CONTAINERID");
p.ITEMSKU = DBHelper_SqlServer.GetDataValue <string>(dr, "ITEMSKU");
p.ITEMDESC = DBHelper_SqlServer.GetDataValue <string>(dr, "ITEMDESC");
p.ITEMQTY = DBHelper_SqlServer.GetDataValue <decimal>(dr, "ITEMQTY");
#endregion
rtn.Add(p);
}
}
}
catch
{
}
return(rtn);
}
private static List <PW_User> DoQuery(string sql_str)
{
List <PW_User> rtn = new List <PW_User>();
DBHelper DBHelper_SqlServer = new DBLink();
try
{
using (IDataReader dr = DBHelper_SqlServer.ExecuteReader(sql_str))
{
while (dr.Read())
{
var p = new PW_User();
p.ID = DBHelper_SqlServer.GetDataValue <string>(dr, "ID");
p.USERNAME = DBHelper_SqlServer.GetDataValue <string>(dr, "USERNAME");
p.PASSWORD = DBHelper_SqlServer.GetDataValue <string>(dr, "PASSWORD");
p.USERGROUP = DBHelper_SqlServer.GetDataValue <string>(dr, "USERGROUP");
p.DEPARTMENT = DBHelper_SqlServer.GetDataValue <string>(dr, "DEPARTMENT");
p.UPDATETIME = DBHelper_SqlServer.GetDataValue <string>(dr, "UPDATETIME");
p.PASSWORDUPDATETIME = DBHelper_SqlServer.GetDataValue <string>(dr, "PASSWORDUPDATETIME");
p.PASSWORDEXPIRETIME = DBHelper_SqlServer.GetDataValue <string>(dr, "PASSWORDEXPIRETIME");
p.LASTLOGINTIME = DBHelper_SqlServer.GetDataValue <string>(dr, "LASTLOGINTIME");
p.ISLOGININ = DBHelper_SqlServer.GetDataValue <string>(dr, "ISLOGININ");
p.HANDSHAKE = DBHelper_SqlServer.GetDataValue <string>(dr, "HANDSHAKE");
p.PW_BACKUP1 = DBHelper_SqlServer.GetDataValue <string>(dr, "PW_BACKUP1");
p.PW_BACKUP2 = DBHelper_SqlServer.GetDataValue <string>(dr, "PW_BACKUP2");
p.PW_BACKUP3 = DBHelper_SqlServer.GetDataValue <string>(dr, "PW_BACKUP3");
rtn.Add(p);
}
}
}
catch
{
}
return(rtn);
}
/// <summary>
/// 根据ID号修改FLAG
/// </summary>
/// <param name="alarmId">报警序列号</param>
/// <param name="flag">标记值</param>
/// <returns></returns>
public static bool UpdateOneFlagByAlarmId(string alarmId, string flag)
{
StringBuilder sb = new StringBuilder();
sb.Append(" update DEVICE_ALERT set ");
sb.Append("SYSTEMFLAG=:SYSTEMFLAG ");
sb.Append(" where ID=:ID ");
DbCommand cmd = new SqlCommand();
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("SYSTEMFLAG", DbType.String)
{
Value = flag
});
cmd.Parameters.Add(new SqlParameter("ID", DbType.String)
{
Value = alarmId
});
DBHelper DBHelper_SqlServer = new DBLink();
int val = DBHelper_SqlServer.ExecuteNonQuery(cmd);
if (val > 0)
{
return(true);
}
else
{
return(false);
}
}
public IActionResult SubmitAddLink(DBLink mydblink, string name, string link, int categoryID, bool pinnedToTop)
{
if (HttpContext.Session.GetString("auth") != "true")
{
return(RedirectToAction("Login", "Login"));
}
if (!ModelState.IsValid)
{
return(RedirectToAction("AdminIndex"));
}
try {
Console.WriteLine("from submit" + name + link + categoryID + pinnedToTop);
linkManager.Add(mydblink);
linkManager.SaveChanges();
if (pinnedToTop == true)
{
DBPinnedLink dbpinlink = new DBPinnedLink();
dbpinlink.categoryID = categoryID;
dbpinlink.name = name;
dbpinlink.link = link;
pinnedLinkManager.Add(dbpinlink);
pinnedLinkManager.SaveChanges();
}
} catch (Exception e) {
Console.WriteLine("Problem adding link" + e);
TempData["adminFeedBack"] = "Unsuccessful addition of link";
} finally {
TempData["adminFeedBack"] = "Successful addition of link";
}
return(RedirectToAction("AdminIndex"));
}
private static List <IM_Container> DoQuery(string sql_str)
{
List <IM_Container> rtn = new List <IM_Container>();
try
{
DBHelper DBHelper_SqlServer = new DBLink();
using (IDataReader dr = DBHelper_SqlServer.ExecuteReader(sql_str))
{
while (dr.Read())
{
var p = new IM_Container();
p.CONTAINERID = DBHelper_SqlServer.GetDataValue <string>(dr, "CONTAINERID");
p.CONTAINERDESC = DBHelper_SqlServer.GetDataValue <string>(dr, "CONTAINERDESC");
p.CONTAINERTYPE = DBHelper_SqlServer.GetDataValue <string>(dr, "CONTAINERTYPE");
p.LENGTH = DBHelper_SqlServer.GetDataValue <decimal>(dr, "LENGTH");
p.WIDTH = DBHelper_SqlServer.GetDataValue <decimal>(dr, "WIDTH");
p.HEIGHT = DBHelper_SqlServer.GetDataValue <decimal>(dr, "HEIGHT");
p.MAXWEIGHT = DBHelper_SqlServer.GetDataValue <decimal>(dr, "MAXWEIGHT");
p.VOID = DBHelper_SqlServer.GetDataValue <int>(dr, "VOID");
p.STATUS = DBHelper_SqlServer.GetDataValue <int>(dr, "STATUS");
p.WAREHOUSENO = DBHelper_SqlServer.GetDataValue <string>(dr, "WAREHOUSENO");
p.ONLINEMODEL = DBHelper_SqlServer.GetDataValue <string>(dr, "ONLINEMODEL");
p.UPDATETIME = DBHelper_SqlServer.GetDataValue <string>(dr, "UPDATETIME");
p.UPDATEUSER = DBHelper_SqlServer.GetDataValue <string>(dr, "UPDATEUSER");
rtn.Add(p);
}
}
}
catch (Exception ex)
{
}
return(rtn);
}
private static List <MODULE_VS_GROUP> DoQuery(string sql_str)
{
List <MODULE_VS_GROUP> rtn = new List <MODULE_VS_GROUP>();
try
{
DBHelper DBHelper_SqlServer = new DBLink();
using (IDataReader dr = DBHelper_SqlServer.ExecuteReader(sql_str))
{
while (dr.Read())
{
var p = new MODULE_VS_GROUP();
p.ID = DBHelper_SqlServer.GetDataValue <string>(dr, "ID");
p.GROUP_NAME = DBHelper_SqlServer.GetDataValue <string>(dr, "GROUP_NAME");
p.GROUP_DESC = DBHelper_SqlServer.GetDataValue <string>(dr, "GROUP_DESC");
p.MODULE_ID = DBHelper_SqlServer.GetDataValue <string>(dr, "MODULE_ID");
p.MODULE_NAME = DBHelper_SqlServer.GetDataValue <string>(dr, "MODULE_NAME");
rtn.Add(p);
}
}
}
catch
{
}
return(rtn);
}
/// <summary>
/// 废除上一次任务的有效性，当完成一个任务时，会先将以前的上一次任务（LastTask）作废，将刚刚完成的任务设置为最新上一次任务。
/// </summary>
/// <param name="DeviceID"></param>
/// <returns></returns>
public static bool SetLastTaskVoidByDeviceID(string DeviceID)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("update od_task set IsLastTask='N' where DoDeviceID='" + DeviceID + "' and IsLastTask='Y' and HadFinish='Y' ");
DbCommand cmd = new SqlCommand();
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
//cmd.Parameters.Add(new OracleParameter("@DeviceID",OracleDbType.NVarchar2,100) { Value = DeviceID });
DBHelper DBHelper_SqlServer = new DBLink();
int val = DBHelper_SqlServer.ExecuteNonQuery(cmd);
if (val > 0)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
private static List <Device_Alert> DoQuery(string sql_str)
{
List <Device_Alert> rtn = new List <Device_Alert>();
try
{
DBHelper DBHelper_SqlServer = new DBLink();
using (IDataReader dr = DBHelper_SqlServer.ExecuteReader(sql_str))
{
while (dr.Read())
{
var p = new Device_Alert();
#region 逐个赋值
p.ID = DBHelper_SqlServer.GetDataValue <string>(dr, "ID");
p.DEVICEID = DBHelper_SqlServer.GetDataValue <string>(dr, "DEVICEID");
p.ALERTID = DBHelper_SqlServer.GetDataValue <string>(dr, "ALERTID");
p.ALERTNAME = DBHelper_SqlServer.GetDataValue <string>(dr, "ALERTNAME");
p.CREATETIME = DBHelper_SqlServer.GetDataValue <string>(dr, "CREATETIME");
p.FINISH_TIME = DBHelper_SqlServer.GetDataValue <string>(dr, "FINISH_TIME");
p.TIME_OF_DURATION = DBHelper_SqlServer.GetDataValue <string>(dr, "TIME_OF_DURATION");
p.SYSTEMFLAG = DBHelper_SqlServer.GetDataValue <string>(dr, "SYSTEMFLAG");
#endregion
rtn.Add(p);
}
}
}
catch
{
}
return(rtn);
}
private static List <MODULE> DoQuery(string sql_str)
{
List <MODULE> rtn = new List <MODULE>();
try
{
DBHelper DBHelper_SqlServer = new DBLink();
using (IDataReader dr = DBHelper_SqlServer.ExecuteReader(sql_str))
{
while (dr.Read())
{
var p = new MODULE();
p.ID = DBHelper_SqlServer.GetDataValue <string>(dr, "ID");
p.MODULE_NAME = DBHelper_SqlServer.GetDataValue <string>(dr, "MODULE_NAME");
p.MODULE_DESCRIBE = DBHelper_SqlServer.GetDataValue <string>(dr, "MODULE_DESCRIBE");
p.TAGNAME = DBHelper_SqlServer.GetDataValue <string>(dr, "TAGNAME");
p.STATUS = DBHelper_SqlServer.GetDataValue <string>(dr, "STATUS");
rtn.Add(p);
}
}
}
catch
{
}
return(rtn);
}
public IActionResult SubmitEditLink(DBLink mydblink, string categoryID, int linkID, bool pinnedToTop, string name, string link)
{
if (HttpContext.Session.GetString("auth") != "true")
{
return(RedirectToAction("Login", "Login"));
}
if (!ModelState.IsValid)
{
return(RedirectToAction("AdminIndex"));
}
try {
int newcategoryID = Convert.ToInt32(categoryID);
DBLink editedLink = linkManager.dblinks.Find(id => id.linkID == linkID);
editedLink.categoryID = newcategoryID;
editedLink.name = name;
editedLink.link = link;
linkManager.SaveChanges();
if (pinnedToTop == true)
{
DBPinnedLink dbpinlink = new DBPinnedLink();
dbpinlink.categoryID = newcategoryID;
dbpinlink.name = name;
dbpinlink.link = link;
pinnedLinkManager.Add(dbpinlink);
pinnedLinkManager.SaveChanges();
}
} catch (Exception e) {
Console.WriteLine("Problem editing link" + e);
TempData["adminFeedBack"] = "Unsuccessful edit of link";
} finally {
TempData["adminFeedBack"] = "Successful edit of link";
}
return(RedirectToAction("AdminIndex"));
}
public MySqlDataReader readSaleslead(int salesID)
{
DBLink.openConnection();
string query = "SELECT * FROM `salesleads`.`salesleads` WHERE `SalesID`='" + salesID + "';";
MySqlDataReader saleslead = DBLink.executeReadQuarry(query);
return(saleslead);
}
public SalesLead readFromDatabase(int salesLeadsID)
{
DBLink.openConnection();
string sqlUser = "******" + salesLeadsID + ";";
MySqlDataReader salesleadData = DBLink.executeReadQuarry(sqlUser);
if (salesleadData.Read())
{
int customerID = salesleadData.GetInt32(1);
int productID = salesleadData.GetInt32(2);
SalesLead existingSalesLead = new SalesLead(null, null, null);
existingSalesLead.SalesID = salesleadData.GetInt32(0);
existingSalesLead.getSalesActivity(ActivityType.SalesIssue).ActivityDate = Rules.dbNullDateField(salesleadData, 3);
existingSalesLead.RevenueType = salesleadData.GetString(4);
existingSalesLead.MonthlyRevenue = salesleadData.GetDecimal(5);
existingSalesLead.HadTest = Rules.yesnoToBoolean(salesleadData.GetString(6));
existingSalesLead.ProjectRevenue = salesleadData.GetDecimal(7);
existingSalesLead.ProjectPaid = salesleadData.GetDecimal(8);
existingSalesLead.getSalesActivity(ActivityType.CustomerConfirm).ActivityDate = Rules.dbNullDateField(salesleadData, 9);
existingSalesLead.getSalesActivity(ActivityType.AgreementSign).ActivityDate = Rules.dbNullDateField(salesleadData, 11);
existingSalesLead.getSalesActivity(ActivityType.DSP).ActivityDate = Rules.dbNullDateField(salesleadData, 12);
existingSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDetails = salesleadData.GetString(13);
existingSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDate = Rules.dbNullDateField(salesleadData, 14);
existingSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDetails = salesleadData.GetString(15);
existingSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDate = Rules.dbNullDateField(salesleadData, 16);
existingSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDate = Rules.dbNullDateField(salesleadData, 17);
existingSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDetails = salesleadData.GetString(18);
existingSalesLead.CustomerFeedBack = salesleadData.GetString(19);
existingSalesLead.Discount = salesleadData.GetDecimal(20);
existingSalesLead.Notes = salesleadData.GetString(21);
int purchaseOrderFileSize = salesleadData.GetInt32(22);
byte[] binaryData = new byte[purchaseOrderFileSize];
if (purchaseOrderFileSize > 0)
{
salesleadData.GetBytes(10, 0, binaryData, 0, purchaseOrderFileSize);
}
existingSalesLead.PurchaseOrderData = binaryData;
Administration.Customer.Customer customer = Administration.Customer.CustomerDA.getInstance().readFromDatabase(customerID);
Administration.Product.Product product = Administration.Product.ProductDA.getInstance().readFromDatabase(productID);
Administration.User.User accManager = Administration.User.UserDA.getInstance().readFromDatabase(customer.AccountManager);
existingSalesLead.Customer = customer;
existingSalesLead.Product = product;
existingSalesLead.AccountManager = accManager;
return(existingSalesLead);
}
return(null);
}
public bool addToDatabase(SalesLead newSalesLead)
{
string sqlAddCustomer = "INSERT INTO `salesleads`.`customerproduct` (`SalesLeadsID`,`CustomerID`, `ProductID`, `IssueDate`, `RevenueType`, `MonthlyRevenue`, `HadTest`, `ProjectRevenue`,`ProjectPaid`, `CustomerConfirmDate`, `PurchaseOrderLocation`, `AgreementSignDate`,`DSP`,`FirstBillIssued`,`BillIssueDate`,`SuccessfullClose`,`ClosedDate`,`DisconnectedDate`,`DisconnectReason`,`CoustomerFeedBack`,`Discount`,`Notes`) VALUES (NULL, '" + newSalesLead.Customer.CustomerID + "', '" + newSalesLead.Product.ProductID + "', " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.SalesIssue).ActivityDate) + ", '" + newSalesLead.RevenueType + "', '" + newSalesLead.MonthlyRevenue + "', '" + Rules.boolToYesNo(newSalesLead.HadTest) + "', '" + newSalesLead.ProjectRevenue + "', '" + newSalesLead.ProjectPaid + "', " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.CustomerConfirm).ActivityDate) + ", ?FileData, " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.AgreementSign).ActivityDate) + ", " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.DSP).ActivityDate) + ", '" + newSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDetails + "', " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDate) + ", '" + newSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDetails + "', " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDate) + ", " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDate) + ", '" + newSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDetails + "', '" + newSalesLead.CustomerFeedBack + "', '" + newSalesLead.Discount + "', '" + newSalesLead.Notes + "');";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlAddCustomer, "?FileData", newSalesLead.PurchaseOrderData);
DBLink.closeConnection();
return(result);
}
public bool updateToDatabaseQuotationData(Quotation existingQuotation)
{
string sqlUpdateQuotation = "UPDATE `salesleads`.`quatation` SET `QuotationLocation` = ?FileData WHERE `quatation`.`QuatationID` = " + existingQuotation.QuotationID + ";";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlUpdateQuotation, "?FileData", existingQuotation.QuotationData);
DBLink.closeConnection();
return(result);
}
public bool updateToDatabase(SalesLead existingSalesLead)
{
string sqlUpdateCustomer = "UPDATE `salesleads`.`customerproduct` SET `CustomerID` = '" + existingSalesLead.Customer.CustomerID + "',`ProductID` = '" + existingSalesLead.Product.ProductID + "', `IssueDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.SalesIssue).ActivityDate) + ", `RevenueType` = '" + existingSalesLead.RevenueType + "', `MonthlyRevenue` = '" + existingSalesLead.MonthlyRevenue + "', `HadTest` = '" + Rules.boolToYesNo(existingSalesLead.HadTest) + "', `ProjectRevenue` = '" + existingSalesLead.ProjectRevenue + "', `ProjectPaid` = '" + existingSalesLead.ProjectPaid + "', `CustomerConfirmDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.CustomerConfirm).ActivityDate) + ",`PurchaseOrderLocation`=?FileData, `AgreementSignDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.AgreementSign).ActivityDate) + ", `DSP` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.DSP).ActivityDate) + ", `FirstBillIssued` = '" + existingSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDetails + "', `BillIssueDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDate) + ", `SuccessfullClose` = '" + existingSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDetails + "', `ClosedDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDate) + ", `DisconnectedDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDate) + ", `DisconnectReason` = '" + existingSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDetails + "', `CoustomerFeedBack` = '" + existingSalesLead.CustomerFeedBack + "', `Discount` = '" + existingSalesLead.Discount + "', `Notes` = '" + existingSalesLead.Notes + "' WHERE `customerproduct`.`SalesLeadsID` = " + existingSalesLead.SalesID + ";";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlUpdateCustomer, "?FileData", existingSalesLead.PurchaseOrderData);
DBLink.closeConnection();
return(result);
}
public bool updateToDatabase(Product existingProduct)
{
string sqlUpdateCustomer = "UPDATE `salesleads`.`product` SET `ProductName` = '" + existingProduct.ProductName + "', `Category` = '" + existingProduct.ProductCategory + "', `InitialPrice` = '" + existingProduct.InitialPrice + "', `ProductDetails` = '" + existingProduct.ProductDetails + "' WHERE `product`.`ProductID` = " + existingProduct.ProductID + ";";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlUpdateCustomer);
DBLink.closeConnection();
return(result);
}
public bool addToDatabase(Product newProduct)
{
string sqlAddCustomer = "INSERT INTO `salesleads`.`product` (`ProductID`, `ProductName`, `Category`, `InitialPrice`, `ProductDetails`) VALUES (NULL, '" + newProduct.ProductName + "', '" + newProduct.ProductCategory + "', '" + newProduct.InitialPrice + "', '" + newProduct.ProductDetails + "');";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlAddCustomer);
DBLink.closeConnection();
return(result);
}
public bool updateToDatabase(User existingUser)
{
string sqlUpdateUser = "******" + existingUser.Name + "', `Permissions` = '" + existingUser.Permissions + "', `UserRating` = '" + existingUser.UserRating + "', `SalingsPerMonth` = '" + existingUser.MonthlyRevnue + "', `Predecessor` = '" + existingUser.Predecessor + "', `Notes` = '" + existingUser.Notes + "' WHERE `user`.`UserName` = '" + existingUser.UserName + "';";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlUpdateUser);
DBLink.closeConnection();
return(result);
}
public bool addToDatabase(User newUser)
{
string sqlAddUser = "******" + newUser.UserName + "', '" + newUser.Name + "', '" + newUser.Permissions + "', '" + newUser.UserRating + "', '" + newUser.MonthlyRevnue + "','" + newUser.Predecessor + "', '" + newUser.Notes + "');";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlAddUser);
DBLink.closeConnection();
return(result);
}
public bool addToDatabase(Comment newComment)
{
string sqlAddComment = "INSERT INTO `salesleads`.`comment` (`Content`, `UserName`, `SalesLeadsID`, `PostDate`, `RealName`) VALUES ('" + newComment.Content + "', '" + newComment.UserName + "', " + newComment.SalesleadsID + ", " + Rules.toSQLDate(newComment.PostDate) + ", '" + newComment.RealName + "');";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlAddComment);
DBLink.closeConnection();
return(result);
}
public bool updateToDatabase(Quotation existingQuotation)
{
string sqlUpdateQuotation = "UPDATE `salesleads`.`quatation` SET `SalesLeadsID` = '" + existingQuotation.SalesleadID + "', `QuatationDate` = '" + existingQuotation.QuotationDate + "', `QuotationStatus` = '" + existingQuotation.QuotationStatus + "' WHERE `quatation`.`QuatationID` = " + existingQuotation.QuotationID + ";";
DBLink.openConnection();
bool result = DBLink.executeWriteQuarry(sqlUpdateQuotation);
DBLink.closeConnection();
return(result);
}
