private int GetNewID()
{
string tblName = "Sales_JobMasterList_Invoice";
int nID = SqlCommon.GetNewlyInsertedRecordID(tblName);
return(nID);
}
public void CreateNew()
{
InsertRecord();
int myNewID = SqlCommon.GetNewlyInsertedRecordID("Invoice_Item");
UpdateSerialID(myNewID);
}
public void InsertItem()
{
InsertWorkorderItem();
int newWoItemID = SqlCommon.GetNewlyInsertedRecordID("WO_Item");
NewWorkItemID = newWoItemID;
GenerateSerialID(newWoItemID);
}
public override int CopyTitle(int myParentID, int newParentID, int myID)
{
var cqt = new CopyQuoteTitle(myID);
cqt.Copy(myParentID, myID);
string tblName = "Sales_JobMasterList_QuoteRev";
int nID = SqlCommon.GetNewlyInsertedRecordID(tblName);
return(nID);
}
public void GetAllCheckLists(int woId, string sfWorkOrderID)
{
try
{
//create service client to call API endpoint
using (enterprise.SoapClient queryClient = new enterprise.SoapClient("Soap", apiAddr))
{
//create SQL query statement
string query = "SELECT Id, Check_List_Item__c, Content__c "
+ " FROM Site_Check_Check_List__c where Work_Order_Name__c = '" + sfWorkOrderID + "'";
enterprise.QueryResult result;
queryClient.query(
header, //sessionheader
null, //queryoptions
null, //mruheader
null, //packageversion
query, out result);
/* if no any record, return */
if (result.size == 0) return;
//cast query results
IEnumerable<enterprise.Site_Check_Check_List__c> checkList = result.records.Cast<enterprise.Site_Check_Check_List__c>();
List<string> items = new List<string>();
foreach (var wl in checkList)
{
items.Add(wl.Id);
int checkListID = CommonMethods.GetMISID(TableName.WO_WORKORDER_CHECKLIST_DATATABLE_SC_C, wl.Id, sfWorkOrderID, salesForceProjectID);
if (checkListID == 0)
{
InsertCheckList(woId, wl.Check_List_Item__c, wl.Content__c);
int newId = SqlCommon.GetNewlyInsertedRecordID(TableName.WO_WORKORDER_CHECKLIST_DATATABLE);
if (newId > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.WO_WORKORDER_CHECKLIST_DATATABLE_SC_C, wl.Id, newId.ToString(), sfWorkOrderID, salesForceProjectID);
}
}
else
{
UpdateCheckListInstruction(checkListID, wl.Check_List_Item__c, wl.Content__c);
}
}
/* use the same function with Work Shop Instruction */
DeleteAllDeletedCheckLists(items.ToArray(), sfWorkOrderID);
LogMethods.Log.Debug("GetAllCheckLists:Debug:" + "Done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("GetAllCheckLists:Error:" + e.Message);
}
}
public void GetAllInspectorInstructions(int woId, string sfWorkOrderID)
{
try
{
//create service client to call API endpoint
using (enterprise.SoapClient queryClient = new enterprise.SoapClient("Soap", apiAddr))
{
//create SQL query statement
string query = "SELECT Id, Category__c, Final_Instruction__c, Instruction__c "
+ " FROM Inspector_Instruction__c where Work_Order_Name__c = '" + sfWorkOrderID + "'";
enterprise.QueryResult result;
queryClient.query(
header, //sessionheader
null, //queryoptions
null, //mruheader
null, //packageversion
query, out result);
/* if no any record, return */
if (result.size == 0) return;
//cast query results
IEnumerable<enterprise.Inspector_Instruction__c> inspectorList = result.records.Cast<enterprise.Inspector_Instruction__c>();
List<string> items = new List<string>();
foreach (var wl in inspectorList)
{
items.Add(wl.Id);
/* check if the work order exists */
int workShopID = CommonMethods.GetMISID(TableName.WO_Instruction_DataTable_SC_I, wl.Id, sfWorkOrderID, salesForceProjectID);
if (workShopID == 0)
{
InsertInspectorInstruction(woId, wl.Category__c, wl.Instruction__c, wl.Final_Instruction__c);
int newId = SqlCommon.GetNewlyInsertedRecordID(TableName.WO_Instruction_DataTable);
if (newId > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.WO_Instruction_DataTable_SC_I, wl.Id, newId.ToString(), sfWorkOrderID, salesForceProjectID);
}
}
else
{
UpdateInspectorInstruction(workShopID, wl.Category__c, wl.Instruction__c, wl.Final_Instruction__c);
}
}
DeleteAllDeletedInspectorInstructions(items.ToArray(), sfWorkOrderID);
LogMethods.Log.Debug("GetAllInspectorInstructions:Debug:" + "Done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("GetAllInspectorInstructions:Error:" + e.Message);
}
}
private void HandleInvoiceService(int invoiceID, string sfInvoiceID, enterprise.QueryResult result)
{
try
{
using (enterprise.SoapClient queryClient = new enterprise.SoapClient("Soap", apiAddr))
{
if (result == null || (result != null && result.size == 0))
{
return;
}
IEnumerable <enterprise.Service_Cost__c> serviceList = result.records.Cast <enterprise.Service_Cost__c>();
var svc = new FsService(invoiceID, "Invoice");
List <string> services = new List <string>();
foreach (var sl in serviceList)
{
services.Add(sl.Id);
long estServiceID = CommonMethods.GetMISID(TableName.Fw_Quote_Service, sl.Id, sfInvoiceID, salesForceProjectID);
if (estServiceID == 0)
{
int printOrder = svc.GetQsMaxPrintOrder() + 1;
svc.InsertRecord(Convert.ToInt32(sl.Service_Name__r.MIS_Service_Number__c),
sl.Service_Cost__c1 == null ? "0" : sl.Service_Cost__c1.ToString(),
1,
sl.Service_Detail__c == null ? "" : sl.Service_Detail__c,
sl.Service_Name__r.Name,
sl.Service_Cost__c1 == null ? "0" : sl.Service_Cost__c1.ToString(),
printOrder
);
int qs_id = SqlCommon.GetNewlyInsertedRecordID(TableName.Fw_Quote_Service);
if (qs_id > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.Fw_Quote_Service, sl.Id, qs_id.ToString(), sfInvoiceID, salesForceProjectID);
}
}
else
{
UpdateInvoiceService(estServiceID, sl.Service_Cost__c1, sl.Service_Detail__c, sl.Service_Name__r.Name, Convert.ToInt16(sl.Service_Name__r.MIS_Service_Number__c), sl.Note__c);
}
}
DeleteAllDeletedInvoiceServices(services.ToArray(), sfInvoiceID);
LogMethods.Log.Debug("HandleInvoiceService:Debug:" + "Done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("HandleInvoiceService:Error:" + e.Message);
}
}
private void HandleInvoiceItem(int invoiceID, int estRevID, string sfInvoiceID, enterprise.QueryResult result)
{
try
{
//create service client to call API endpoint
using (enterprise.SoapClient queryClient = new enterprise.SoapClient("Soap", apiAddr))
{
if (result == null || (result != null && result.size == 0))
{
return;
}
//cast query results
IEnumerable <enterprise.Item__c> itemList = result.records.Cast <enterprise.Item__c>();
List <string> items = new List <string>();
//show results
foreach (var il in itemList)
{
items.Add(il.Id);
int itemIDTemp = CommonMethods.GetMISID(TableName.Invoice_Item, il.Id, sfInvoiceID, salesForceProjectID);
if (itemIDTemp == 0)
{
InvoiceItemBlank inv = new InvoiceItemBlank(invoiceID);
inv.CreateNew();
int newId = SqlCommon.GetNewlyInsertedRecordID(TableName.Invoice_Item);
if (newId > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.Invoice_Item, il.Id, newId.ToString(), sfInvoiceID, salesForceProjectID);
}
itemIDTemp = newId;
}
if (itemIDTemp != 0)
{
UpdateInvoiceItem(estRevID, il.Id, itemIDTemp, il.Item_Name__c, il.Requirement__c, il.Item_Description__c, il.Item_Cost__c, il.Quantity__c, il.Item_Order__c);
}
}
/* delete work order items which has been removed out of work order */
DeleteAllDeletedInvoiceItems(items.ToArray(), sfInvoiceID);
LogMethods.Log.Debug("HandleInvoiceItem:Debug:" + "Done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("HandleInvoiceItem:Error:" + e.Message);
}
}
private void HandleAccountContact(int jobID, int jcID, string contactID, int customerRowID, string firstName, string lastName, string phone, string accountID, int type)
{
try
{
/* contact info */
int vContactID = CommonMethods.GetMISID(TableName.Customer_Contact, contactID, accountID, salesForceProjectID);
if (vContactID == 0)
{
/* check if the contact is existent */
vContactID = CommonMethods.GetContactCompanyMISID(TableName.Customer_Contact, contactID, accountID);
if (vContactID == 0)
{
/* add new contact */
FsCustomerContact cc = new FsCustomerContact(customerRowID);
cc.InsertContact();
int contact_id = SqlCommon.GetNewlyInsertedRecordID(TableName.Customer_Contact);
if (contact_id > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.Customer_Contact, contactID, contact_id.ToString(), accountID, salesForceProjectID);
}
vContactID = contact_id;
}
}
/* update */
CUSTOMER_CONTACT customer_contact = _db.CUSTOMER_CONTACT.FirstOrDefault(x => x.CONTACT_ID == vContactID);
if (customer_contact != null)
{
customer_contact.CONTACT_FIRST_NAME = firstName;
customer_contact.CONTACT_LAST_NAME = lastName;
customer_contact.CONTACT_PHONE = phone;
_db.Entry(customer_contact).State = EntityState.Modified;
_db.SaveChanges();
}
UpdateSales_JobMasterList_Customer(jcID, jobID, vContactID, customerRowID, type);
LogMethods.Log.Debug("HandleAccountContact:Debug:" + "Done");
}
catch (Exception e)
{
LogMethods.Log.Error("HandleAccountContact:Error:" + e.Message);
}
}
public void GetAllCheckLists(int woId, string sfWorkOrderID, enterprise.QueryResult result)
{
try
{
//create service client to call API endpoint
using (enterprise.SoapClient queryClient = new enterprise.SoapClient("Soap", apiAddr))
{
if (result == null || (result != null && result.size == 0))
{
return;
}
//cast query results
IEnumerable <enterprise.Production_Check_List__c> checkList = result.records.Cast <enterprise.Production_Check_List__c>();
List <string> items = new List <string>();
foreach (var wl in checkList)
{
items.Add(wl.Id);
int checkListID = CommonMethods.GetMISID(TableName.WO_WORKORDER_CHECKLIST_DATATABLE_PC, wl.Id, sfWorkOrderID, salesForceProjectID);
if (checkListID == 0)
{
InsertCheckList(woId, wl.Check_List_Item__c, wl.Content__c, wl.Content_For_Check_List_Item_As_Others__c);
int newId = SqlCommon.GetNewlyInsertedRecordID(TableName.WO_WORKORDER_CHECKLIST_DATATABLE);
if (newId > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.WO_WORKORDER_CHECKLIST_DATATABLE_PC, wl.Id, newId.ToString(), sfWorkOrderID, salesForceProjectID);
}
}
else
{
UpdateCheckListInstruction(checkListID, wl.Check_List_Item__c, wl.Content__c, wl.Content_For_Check_List_Item_As_Others__c);
}
}
/* use the same function with Work Shop Instruction */
DeleteAllDeletedCheckLists(items.ToArray(), sfWorkOrderID);
LogMethods.Log.Debug("GetAllCheckLists:Debug:" + "Done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("GetAllCheckLists:Error:" + e.Message);
}
}
public void GetAllWorkShopInstructions(int woId, string sfWorkOrderID, enterprise.QueryResult result)
{
try
{
//create service client to call API endpoint
using (enterprise.SoapClient queryClient = new enterprise.SoapClient("Soap", apiAddr))
{
if (result == null || (result != null && result.size == 0))
{
return;
}
//cast query results
IEnumerable <enterprise.WorkShop_Instruction__c> workShopList = result.records.Cast <enterprise.WorkShop_Instruction__c>();
List <string> items = new List <string>();
foreach (var wl in workShopList)
{
items.Add(wl.Id);
/* check if the work order exists */
int workShopID = CommonMethods.GetMISID(TableName.WO_Instruction_DataTable_PW, wl.Id, sfWorkOrderID, salesForceProjectID);
if (workShopID == 0)
{
InsertWorkShopInstruction(woId, wl.Category__c, wl.Instruction__c, wl.Final_Instruction__c);
int newId = SqlCommon.GetNewlyInsertedRecordID(TableName.WO_Instruction_DataTable);
if (newId > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.WO_Instruction_DataTable_PW, wl.Id, newId.ToString(), sfWorkOrderID, salesForceProjectID);
}
}
else
{
UpdateWorkShopInstruction(workShopID, wl.Category__c, wl.Instruction__c, wl.Final_Instruction__c);
}
}
DeleteAllDeletedWorkShopInstructions(items.ToArray(), sfWorkOrderID);
LogMethods.Log.Debug("GetAllWorkShopInstruction:Debug:" + "Done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("GetAllWorkShopInstruction:Error:" + e.Message);
}
}
private void HandleNotes(int jobID, int estRevID, int quoteRevID, enterprise.QueryResult result, string sfQuoteID)
{
try
{
if (result != null)
{
IEnumerable <enterprise.Note__c> quoteList = result.records.Cast <enterprise.Note__c>();
List <string> notes = new List <string>();
foreach (var q in quoteList)
{
notes.Add(q.Id);
int noteID = CommonMethods.GetMISID(TableName.Fw_Quote_Note, q.Id, sfQuoteID, salesForceProjectID);
if (noteID == 0)
{
QuoteNoteCreateNew qnc = new QuoteNoteCreateNew(quoteRevID);
qnc.Insert();
int newNoteId = SqlCommon.GetNewlyInsertedRecordID(TableName.Fw_Quote_Note);
if (newNoteId > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.Fw_Quote_Note, q.Id, newNoteId.ToString(), sfQuoteID, salesForceProjectID);
}
noteID = newNoteId;
}
if (noteID != 0)
{
UpdateNote(q.Title__c, q.Content__c, noteID);
}
}
DeleteAllDeletedQuoteNotes(notes.ToArray(), sfQuoteID);
LogMethods.Log.Debug("HandleNotes:Debug:" + "Done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("HandleNotes:Error:" + e.Message);
}
}
public void GetAllNotes(int woId, enterprise.QueryResult result, string sfWorkOrderID)
{
try
{
if (result != null)
{
IEnumerable<enterprise.AttachedContentNote> noteList = result.records.Cast<enterprise.AttachedContentNote>();
List<string> notes = new List<string>();
foreach (var q in noteList)
{
notes.Add(q.Id);
int noteID = CommonMethods.GetMISID(TableName.WO_ShippingItem_SC, q.Id, sfWorkOrderID, salesForceProjectID);
if (noteID == 0)
{
InsertNote(woId, q.Title, q.TextPreview);
int newNoteId = SqlCommon.GetNewlyInsertedRecordID(TableName.WO_ShippingItem);
if (newNoteId > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.WO_ShippingItem_SC, q.Id, newNoteId.ToString(), sfWorkOrderID, salesForceProjectID);
}
}
else
{
UpdateNote(noteID, q.Title, q.TextPreview);
}
}
DeleteAllDeletedNotes(notes.ToArray(), sfWorkOrderID);
LogMethods.Log.Debug("GetAllNotes:Debug:" + "Done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("GetAllNotes:Error:" + "Done");
}
}
private void HandleLandlord(string sfLandlordID, string addr, string city, string state, string zipCode, string landlordContactName, string phone, string landlordName)
{
try
{
int landlordID = CommonMethods.GetMISID(TableName.PermitLandlord, sfLandlordID, salesForceProjectID);
if (landlordID == 0)
{
// insert to PermitLandlord
PermitLandlord pl = new PermitLandlord();
pl.NAME = landlordName;
pl.ADDR_1 = addr;
pl.CITY = city;
pl.STATE = state;
pl.ZIPCODE = zipCode;
pl.Active = true;
_db.PermitLandlords.Add(pl);
_db.SaveChanges();
int id = SqlCommon.GetNewlyInsertedRecordID(TableName.PermitLandlord);
landlordID = id;
if (id > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.PermitLandlord, sfLandlordID, id.ToString(), salesForceProjectID);
}
}
else
{
var item = _db.PermitLandlords.Where(x => x.ROWID == landlordID).FirstOrDefault();
if (item != null)
{
item.NAME = landlordName;
item.ADDR_1 = addr;
item.CITY = city;
item.STATE = state;
item.ZIPCODE = zipCode;
_db.Entry(item).State = EntityState.Modified;
_db.SaveChanges();
}
}
/* landlord contact */
int landlordContactID = CommonMethods.GetMISID(TableName.PermitLandlordContact, sfLandlordID, salesForceProjectID);
if (landlordContactID == 0)
{
/* add landloard contact */
PermitLandlordContact plc = new PermitLandlordContact();
plc.ROWID = landlordID;
plc.CONTACT_FIRST_NAME = landlordContactName;
plc.CONTACT_PHONE = phone;
_db.PermitLandlordContacts.Add(plc);
_db.SaveChanges();
int id = SqlCommon.GetNewlyInsertedRecordID(TableName.PermitLandlordContact);
if (id > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.PermitLandlordContact, sfLandlordID, id.ToString(), salesForceProjectID);
}
}
else
{
var item = _db.PermitLandlordContacts.Where(x => x.CONTACT_ID == landlordContactID).FirstOrDefault();
if (item != null)
{
item.CONTACT_FIRST_NAME = landlordContactName;
item.CONTACT_PHONE = phone;
_db.Entry(item).State = EntityState.Modified;
_db.SaveChanges();
}
}
}
catch (Exception e)
{
LogMethods.Log.Error("HandleLandLord:Error:" + e.Message);
}
}
public void Generate()
{
var connectionSQL = new SqlConnection(SalesCenterConfiguration.ConnectionString);
const string sqlSelectString2 = "SELECT * FROM [Quote_Item] WHERE ([quoteItemID] = 0) ";
var selectCommand2 = new SqlCommand(sqlSelectString2, connectionSQL);
var adapter2 = new SqlDataAdapter(selectCommand2);
var ds2 = new DataSet();
ds2.Tables.Clear();
try
{
connectionSQL.Open();
adapter2.Fill(ds2, "t2");
DataRow rowNew = ds2.Tables["t2"].NewRow();
rowNew["quoteRevID"] = _quoteRevID;
rowNew["estItemID"] = _row["estItemID"];
rowNew["qiPrintOrder"] = _row["estItemNo"];
rowNew["quoteOption"] = _row["ItemOption"];
rowNew["isFinal"] = _row["isFinalOption"];
int fwJobTypeRequirementID = Convert.ToInt32(_row["RequirementID"]);
var sp = new SupplyType(fwJobTypeRequirementID);
rowNew["supplyType"] = sp.QuoteSupplyTypeID;
rowNew["qiItemTitle"] = _row["ProductName"];
double qiAmount = MyConvert.ConvertToDouble(_row["PriceA"]);
rowNew["qiAmount"] = qiAmount;
rowNew["qiUnitPrice"] = 0;
rowNew["qiDescription"] = "";
rowNew["qiQty"] = _row["Qty"];
rowNew["qiQtyPC"] = 0;
rowNew["Requirement"] = _row["RequirementID"];
rowNew["Position"] = "Indoor";
rowNew["recordType"] = "Q";
rowNew["quoteOptionText"] = _row["ItemOption"];
rowNew["qiAmountText"] = qiAmount.ToString("F2");
rowNew["nameDetailsID"] = _row["ProductID"];
rowNew["BySubcontractor"] = _row["BySubcontractor"];
ds2.Tables["t2"].Rows.Add(rowNew);
//4. Write ds2, back to DB
var cb = new SqlCommandBuilder(adapter2);
adapter2 = cb.DataAdapter;
adapter2.Update(ds2, "t2");
NewlyInsertedQuoteItemID = SqlCommon.GetNewlyInsertedRecordID("Quote_Item");
}
catch (SqlException ex)
{
string errorLog = ex.Message;
}
finally
{
connectionSQL.Close();
}
}
public void GetAllProjects()
{
try
{
LogMethods.Log.Info("GetAllProjects:Info:" + "Start processing all projects");
//create service client to call API endpoint
using (enterprise.SoapClient queryClient = new enterprise.SoapClient("Soap", apiAddr))
{
//create SQL query statement
string query = "SELECT Id, Project_Number__c, Name, CloseDate, Type, OwnerId, Owner.CommunityNickname, Bidding_Type__c, Bidding_Source__c, Product_Line__c, Middle_Updated_Flag__c, "
+ " Bidding_Due_Date__c, Bidding_Remark__c, Sync__c, Account_Executive__r.CommunityNickname, Project_Coordinator__r.CommunityNickname, "
+ " (SELECT Id, Name, Billing_Company_City__c, Billing_Contact_Name__r.Account.Id, Billing_Company_Name__r.Name, Billing_Company_Name__r.Id, Billing_Company_Postal_Code__c, Billing_Company_Province__c, Billing_Company_Street__c, Billing_Contact_Name__r.FirstName, Billing_Contact_Name__r.LastName, Billing_Contact_Name__r.Id, Billing_Contact_Phone__c, Billing_Company_Country__c, Quoting_Company_City__c, Quoting_Company_Name__r.Name, Quoting_Company_Name__r.Id, Quoting_Contact_Name__r.Account.Id, Quoting_Company_Postal_Code__c, Quoting_Company_Province__c, Quoting_Company_Street__c, Quoting_Contact_Name__r.FirstName, Quoting_Contact_Name__r.LastName, Quoting_Contact_Name__r.Id, Quoting_Contact_Phone__c, Quoting_Company_Country__c,Installing_Company_City__c, Installing_Company_Name__r.Name, Installing_Company_Name__r.Id, Installing_Contact_Name__r.Account.Id, Installing_Company_Postal_Code__c, Installing_Company_Province__c, Installing_Company_Street__c, Installing_Contact_Name__r.FirstName, Installing_Contact_Name__r.LastName, Installing_Contact_Name__r.Id, Installing_Contact_Phone__c, Installing_Company_Country__c, Billing_Account_Intersection__c, Billing_Account_Corner__c, Installing_Account_Intersection__c,Installing_Account_Corner__c,Quoting_Account_Intersection__c,Quoting_Account_Corner__c,Billing_Company_Name__r.Legal_Name__c FROM Bill_Quote_Ships__r), "
+ " (SELECT Id, Number_of_Signs__c, Project_Value_Estimated__c, Remarks__c, Issue_Date__c, Due_Date__c, LandLord__r.Name, LandLord_Contact__r.Name, LandLord_Phone_Number__c, LandLord__r.BillingStreet, LandLord__r.BillingCity, LandLord__r.BillingState, LandLord__r.BillingPostalCode FROM Sign_Permits__r),"
+ " (SELECT Id, Occupation_Start_Time__c, Occupation_End_Time__c, Issue_Date__c, Type_Of_Truck__c, Truck_Weight__c, Foreman_Name__r.Name, Foreman_Phone__c, Remarks__c FROM Hoisting_Permits__r),"
+ " (SELECT Id, Stick_Position_Radius__c, Dept_Of_Holes__c, Issue_Date__c, Due_Date__c, Remarks__c FROM StakeOut_Permits__r),"
+ " (SELECT Id, Name, First_Site_Contact__c, Second_Site_Contact__c, Budget__c, Provided_By__c, Remarks__c, Due_Date__c, Rush__c, Requirement__c, Requirement_As_Other__c, Estimated_Shipping_Cost__c, Shipping_Items_Total_Value__c, Work_Order_Number__c FROM SubContracts__r) "
+ " FROM Opportunity "
+ " WHERE Sync__c = true and Middle_Updated_Flag__c = 1 ";
//+ " WHERE name= '2131 Yonge Street - Exterior and Interior Signage Program'";
enterprise.QueryResult result;
queryClient.query(
header, //sessionheader
null, //queryoptions
null, //mruheader
null, //packageversion
query, out result);
/* if no any record, return */
if (result.size == 0)
{
return;
}
//cast query results
IEnumerable <enterprise.Opportunity> opportunityList = result.records.Cast <enterprise.Opportunity>();
/*
* enterprise.Opportunity[] opp = new enterprise.Opportunity[result.size];
* int i = 0;
* foreach (var opportunity in opportunityList)
* {
* enterprise.Opportunity temp = new enterprise.Opportunity();
* temp.Id = opportunity.Id;
* temp.Middle_Updated_Flag__c = 0;
* temp.Middle_Updated_Flag__cSpecified = true;
* opp[i] = temp;
* i++;
* }
* enterprise.LimitInfo[] l1;
* enterprise.SaveResult[] s1;
* queryClient.update(header, null, null, null, null, null, null, null, null, null, null, null, null, opp, out l1, out s1);
* */
int size = result.size;
int j = 1;
int k = 0;
while (j <= size)
{
int limitArray = (size > 150) ? 150 : size;
size -= limitArray;
enterprise.Opportunity[] opp = new enterprise.Opportunity[limitArray];
int i = 0;
for (int m = k; m < k + limitArray; m++)
{
enterprise.Opportunity temp = new enterprise.Opportunity();
temp.Id = opportunityList.ElementAt(m).Id;
temp.Middle_Updated_Flag__c = 0;
temp.Middle_Updated_Flag__cSpecified = true;
opp[i] = temp;
i++;
}
k += limitArray;
enterprise.LimitInfo[] l1;
enterprise.SaveResult[] s1;
queryClient.update(header, null, null, null, null, null, null, null, null, null, null, null, null, opp, out l1, out s1);
}
//show results
foreach (var opportunity in opportunityList)
{
/* get project owner */
//string un = CommonMethods.GetUserName(opportunity.OwnerId);
string un = (opportunity.Owner.CommunityNickname == null ? "" : opportunity.Owner.CommunityNickname);
FsEmployee fsEmployee = new FsEmployee(un);
if (fsEmployee.EmployeeNumber > 0)
{
LogMethods.Log.Info("GetAllProjects:Info:" + "Processing project name:" + opportunity.Name);
int sales_JobMasterListID = CommonMethods.GetMISID(TableName.Sales_JobMasterList, opportunity.Id, opportunity.Id);
if (sales_JobMasterListID == 0)
{
int jobID = CreateNewProject(fsEmployee.EmployeeNumber);
/* insert data to MISSalesForceMapping */
if (jobID > 0)
{
UpdateProject(jobID, opportunity.CloseDate, fsEmployee.EmployeeNumber, opportunity.Name, opportunity.Type, opportunity.Account_Executive__r, opportunity.Product_Line__c, opportunity.Project_Coordinator__r);
/* update jobnumber */
UpdateJobNumber(jobID, opportunity.Project_Number__c);
CommonMethods.InsertToMISSalesForceMapping(TableName.Sales_JobMasterList, opportunity.Id, jobID.ToString(), opportunity.Id);
}
else
{
LogMethods.Log.Error("GetAllProjects:Error:" + "Cannot create a new project!");
continue;
}
sales_JobMasterListID = jobID;
}
else
{
UpdateProject(sales_JobMasterListID, opportunity.CloseDate, fsEmployee.EmployeeNumber, opportunity.Name, opportunity.Type, opportunity.Account_Executive__r, opportunity.Product_Line__c, opportunity.Project_Coordinator__r);
}
/* for bidding project */
if (opportunity.Type == SalesType.Bid)
{
/* check if the bidding record exists */
int biddingID = GetBiddingID(sales_JobMasterListID);
if (biddingID > 0)
{
//exist
UpdateBiddingProject(biddingID, sales_JobMasterListID, opportunity.Bidding_Type__c, opportunity.Bidding_Source__c, opportunity.Bidding_Due_Date__c, opportunity.Bidding_Remark__c);
}
else
{
InsertBiddingProject(fsEmployee.EmployeeNumber);
UpdateBiddingProject(SqlCommon.GetNewlyInsertedRecordID("Sales_JobMaster_BiddingJob"), Convert.ToInt32(sales_JobMasterListID), opportunity.Bidding_Type__c, opportunity.Bidding_Source__c, opportunity.Bidding_Due_Date__c, opportunity.Bidding_Remark__c);
}
}
/* Bill-Quote-Ship */
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing account and contact");
CustomerMethods cm = new CustomerMethods(opportunity.Id);
cm.GetAllAccounts(opportunity.Id, sales_JobMasterListID, fsEmployee.EmployeeNumber, opportunity.Bill_Quote_Ships__r);
/* Estimation */
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing estimation");
EstimationMethods em = new EstimationMethods(opportunity.Id);
int estRevID = CommonMethods.GetEstRevID(sales_JobMasterListID);
em.GetEstimation(opportunity.Id, estRevID, sales_JobMasterListID, fsEmployee.EmployeeNumber);
/*Drawing */
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing drawing");
DrawingMethods dm = new DrawingMethods(opportunity.Id);
dm.GetAllDrawings(opportunity.Id, estRevID, sales_JobMasterListID, fsEmployee.EmployeeNumber);
/* Quote */
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing quote");
QuoteMethods qm = new QuoteMethods(opportunity.Id);
qm.GetAllQuotes(opportunity.Id, sales_JobMasterListID, estRevID, fsEmployee.EmployeeNumber);
/* Sign/Hoisting/Stakeout permit */
PermitMethods pm = new PermitMethods(opportunity.Id);
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing sign permit");
pm.GetAllSignPermits(opportunity.Id, sales_JobMasterListID, fsEmployee.EmployeeNumber, opportunity.Sign_Permits__r);
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing hoisting permit");
pm.GetAllHoistingPermits(opportunity.Id, sales_JobMasterListID, fsEmployee.EmployeeNumber, opportunity.Hoisting_Permits__r);
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing stakeout permit");
pm.GetAllStakeOutPermits(opportunity.Id, sales_JobMasterListID, fsEmployee.EmployeeNumber, opportunity.StakeOut_Permits__r);
/* WorkOrder */
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing work order");
WorkOrderMethods wo = new WorkOrderMethods(opportunity.Id);
wo.GetAllWorkOrders(opportunity.Id, sales_JobMasterListID, estRevID, fsEmployee.EmployeeNumber);
/* Sub-Contract */
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing SubContract");
SubContractMethods sc = new SubContractMethods(opportunity.Id);
sc.GetAllSubContracts(opportunity.Id, sales_JobMasterListID, fsEmployee.EmployeeNumber, opportunity.SubContracts__r);
/* Invoice */
LogMethods.Log.Debug("GetAllProjects:Debug:" + "Processing invoice");
InvoiceMethods im = new InvoiceMethods(opportunity.Id);
im.GetAllInvoices(opportunity.Id, sales_JobMasterListID, estRevID, fsEmployee.EmployeeNumber);
LogMethods.Log.Info("GetAllProjects:Info:" + "Done: " + opportunity.Name + "<Project Num:" + opportunity.Project_Number__c + ">");
}
else
{
LogMethods.Log.Error("GetAllProjects:Error:" + "User Name: " + un + " does not exist in database");
}
}
LogMethods.Log.Info("GetAllProjects:Info:" + "All projects are done");
}
}
catch (Exception e)
{
LogMethods.Log.Error("GetAllProjects:Error:" + e.Message);
}
}
public void CreateNew()
{
InsertRecord();
NewID = SqlCommon.GetNewlyInsertedRecordID("Quote_Item");
UpdateSerialID(NewID);
}
/**
* Type = 1 => Billing
* Type = 2 => Quoting
* Type = 3 => Shipping
*/
private void HandleAccount(string companyName, string companyStreet, string companyProvince, string companyPostalCode,
string companyCity, string companyCountry, string firstName, string lastName, string phone, string contactID, string intersection, string corner, int misJobID, int employeeNumber, string accountID, int type, string legalName)
{
try
{
int customerID = CommonMethods.GetMISID(TableName.Customer, accountID, salesForceProjectID);
if (customerID == 0)
{
/* Add new billing address */
CUSTOMER customer = new CUSTOMER();
customer.NAME = companyName;
//customer.LEGALNAME_SAMEAS_NAME = true;
if (legalName == null)
{
customer.LEGALNAME_SAMEAS_NAME = true;
customer.LEGAL_NAME = companyName;
}
else
{
customer.LEGALNAME_SAMEAS_NAME = false;
customer.LEGAL_NAME = legalName;//companyName;
}
customer.ADDR_1 = companyStreet;
customer.ADDR_2 = "";
customer.CITY = companyCity;
customer.STATE = companyProvince;
customer.ZIPCODE = companyPostalCode;
customer.COUNTRY = companyCountry;
customer.SalesID = employeeNumber;
customer.Sa1ID = employeeNumber;
customer.TERRITORY = "6"; //other
customer.ACTIVE_FLAG = "Y";
customer.CURRENCY_ID = "CAD";
customer.Intersection = intersection;
switch (corner)
{
case "North West":
customer.NorthWest = true;
break;
case "North East":
customer.NorthEast = true;
break;
case "South West":
customer.SouthWest = true;
break;
case "South East":
customer.SouthEast = true;
break;
}
ProjectCompany cp = new ProjectCompany(misJobID);
cp.Insert(misJobID, 0, false, false, false);
int jcID = SqlCommon.GetNewlyInsertedRecordID(TableName.Sales_JobMasterList_Customer);
/* check if the customer has existed */
int rowID = CommonMethods.GetCompanyMISID(TableName.Customer, accountID);
if (rowID == 0)
{
//if it is not existent
rowID = CreateCustomer(customer, jcID);
}
if (rowID > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.Customer, accountID, rowID.ToString(), salesForceProjectID);
}
HandleAccountContact(misJobID, jcID, contactID, rowID, firstName, lastName, phone, accountID, type);
}
else
{
CUSTOMER customer = _db.CUSTOMERs.FirstOrDefault(x => x.ROWID == customerID);
customer.NAME = companyName;
//customer.LEGALNAME_SAMEAS_NAME = true;
if (legalName == null)
{
customer.LEGALNAME_SAMEAS_NAME = true;
customer.LEGAL_NAME = companyName;
}
else
{
customer.LEGALNAME_SAMEAS_NAME = false;
customer.LEGAL_NAME = legalName;//companyName;
}
customer.ADDR_1 = companyStreet;
customer.ADDR_2 = "";
customer.CITY = companyCity;
customer.STATE = companyProvince;
customer.ZIPCODE = companyPostalCode;
customer.COUNTRY = companyCountry;
customer.SalesID = employeeNumber;
customer.Sa1ID = employeeNumber;
customer.TERRITORY = "6"; //other
customer.ACTIVE_FLAG = "Y";
customer.CURRENCY_ID = "CAD";
customer.Intersection = intersection;
switch (corner)
{
case "North West":
customer.NorthWest = true;
break;
case "North East":
customer.NorthEast = true;
break;
case "South West":
customer.SouthWest = true;
break;
case "South East":
customer.SouthEast = true;
break;
}
_db.Entry(customer).State = EntityState.Modified;
_db.SaveChanges();
Sales_JobMasterList_Customer sales_JobMasterList_Customer = _db.Sales_JobMasterList_Customer.FirstOrDefault(x => x.jobID == misJobID && x.cID == customer.ROWID);
if (sales_JobMasterList_Customer != null)
{
HandleAccountContact(misJobID, sales_JobMasterList_Customer.jcID, contactID, customerID, firstName, lastName, phone, accountID, type);
}
}
LogMethods.Log.Debug("HandleAccount:Debug:" + "Done");
}
catch (Exception e)
{
LogMethods.Log.Error("HandleAccount:Error:" + e.Message);
}
}
public void Copy()
{
string errorLog = "Nothing";
int NumRowsAffected = 0;
//2. Define the destination: InvoiceItem
var ConnectionSQL = new SqlConnection(SalesCenterConfiguration.ConnectionString);
string SqlSelectString1 = "SELECT * FROM [QUOTE_Item] WHERE ([quoteItemID] = @quoteItemID) ";
var SelectCommand1 = new SqlCommand(SqlSelectString1, ConnectionSQL);
var adapter1 = new SqlDataAdapter(SelectCommand1);
adapter1.SelectCommand.Parameters.Add("@quoteItemID", SqlDbType.Int).Value = _myID;
var ds1 = new DataSet();
ds1.Tables.Clear();
try
{
ConnectionSQL.Open();
NumRowsAffected = adapter1.Fill(ds1, "t1");
if (NumRowsAffected != 0)
{
//Destination
//3. Copy
int itemIndex = ds1.Tables["t1"].Columns.Count;
DataRow row = ds1.Tables["t1"].Rows[0];
DataRow rowNew = ds1.Tables["t1"].NewRow();
int i = 0;
for (i = 1; i <= itemIndex - 1; i++)
{
rowNew[i] = row[i];
}
int pn = Convert.ToInt32(rowNew["qiPrintOrder"]);
rowNew["QuoteOption"] = QuoteItemShared.GetMaxOptionNumberOfOneItem(_myParentID, pn);
rowNew["isFinal"] = false;
rowNew["qiAmountText"] = rowNew["qiAmount"];
rowNew["quoteOptionText"] = rowNew["quoteOption"];
ds1.Tables["t1"].Rows.Add(rowNew);
//4. Write ds2, back to DB
var cb = new SqlCommandBuilder(adapter1);
adapter1 = cb.DataAdapter;
NumRowsAffected = adapter1.Update(ds1, "t1");
_newID = SqlCommon.GetNewlyInsertedRecordID("Quote_Item");
}
}
catch (SqlException ex)
{
errorLog = ex.Message;
}
finally
{
ConnectionSQL.Close();
}
}
private void UpdateWorkOrderItem(int estRevID, string salesforceItemID, long workOrderItemID, string itemName, string requirement, string description, double?itemCost, double?quality, double?itemOrder, string PC, string signType)
{
try
{
var workOrderItem = _db.WO_Item.Where(x => x.woItemID == workOrderItemID).FirstOrDefault();
if (workOrderItem != null)
{
workOrderItem.estItemNameText = itemName;
int requirementID = 10;
var jobType = _db.FW_JOB_TYPE.Where(x => x.JOB_TYPE.Trim() == requirement.Trim()).FirstOrDefault();
if (jobType != null)
{
requirementID = jobType.TYPE_ID;
}
else
{
LogMethods.Log.Warn("UpdateWorkOrderItem:Warn:" + "Requirement of " + requirement + " doesn't exist on FW_JOB_TYPE table.");
}
workOrderItem.Requirement = requirementID;
workOrderItem.woDescription = description;
if (quality != null)
{
workOrderItem.qty = Convert.ToInt16(quality);
}
if (PC != null)
{
workOrderItem.qtyPC = Convert.ToInt16(PC);
}
else
{
workOrderItem.qtyPC = 0;
}
if (itemCost != null)
{
workOrderItem.qiAmount = (double)itemCost;
}
long estItemID = CommonMethods.GetEstimationItemID(estRevID, itemName);
if (estItemID != 0)
{
workOrderItem.estItemID = estItemID;
}
if (itemOrder != null)
{
workOrderItem.woPrintOrder = Convert.ToInt16(itemOrder);
}
Product optionDetails = _db.Products.Where(x => x.ProductName.Trim() == signType & x.Active).FirstOrDefault();
if (optionDetails != null)
{
workOrderItem.NameDetailsID = optionDetails.ProductID;
}
_db.Entry(workOrderItem).State = EntityState.Modified;
_db.SaveChanges();
//create service client to call API endpoint
using (enterprise.SoapClient queryClient = new enterprise.SoapClient("Soap", apiAddr))
{
//create SQL query statement
string query = "SELECT Id, Purpose__c, Type__c, Hyperlink__c "
+ " FROM Drawing_Attachment__c "
+ " WHERE Item_Number__c = '" + salesforceItemID + "'";
enterprise.QueryResult result;
queryClient.query(
header, //sessionheader
null, //queryoptions
null, //mruheader
null, //packageversion
query, out result);
/* if no any record, return */
if (result.size == 0)
{
return;
}
//cast query results
IEnumerable <enterprise.Drawing_Attachment__c> drawingAttachmentList = result.records.Cast <enterprise.Drawing_Attachment__c>();
List <string> items = new List <string>();
foreach (var ql in drawingAttachmentList)
{
items.Add(ql.Id);
/* check if the work order exists */
int drawingAttachmentID = CommonMethods.GetMISID(TableName.WO_Item_Drawing, ql.Id, salesforceItemID, salesForceProjectID);
int purpose = 1;
switch (ql.Purpose__c)
{
case "Concept Design":
purpose = 4;
break;
case "Permit Drawing":
purpose = 2;
break;
case "Work-Order Drawing":
purpose = 3;
break;
}
/* remove special character */
string hyperlink = ql.Hyperlink__c.TrimStart();
while (hyperlink[0] != 'P' && hyperlink[0] != 'K' && hyperlink[0] != 'Q' && hyperlink[0] != 'H')
{
hyperlink = hyperlink.Substring(1);
}
string fileName = "";
try
{
fileName = Path.GetFileNameWithoutExtension(hyperlink);
}
catch (Exception e)
{
fileName = "";
}
if (drawingAttachmentID == 0)
{
InsertItemLink(workOrderItemID, hyperlink, purpose, ql.Type__c, fileName);
int temp = SqlCommon.GetNewlyInsertedRecordID(TableName.WO_Item_Drawing);
CommonMethods.InsertToMISSalesForceMapping(TableName.WO_Item_Drawing, ql.Id, temp.ToString(), salesforceItemID, salesForceProjectID);
}
else
{
UpdateItemLink(drawingAttachmentID, hyperlink, purpose, ql.Type__c, fileName);
}
}
DeleteAllDeletedDrawingAttachments(items.ToArray(), salesforceItemID);
LogMethods.Log.Debug("UpdateWorkOrderItem:Debug:" + "Done");
}
}
}
catch (Exception e)
{
LogMethods.Log.Error("UpdateWorkOrderItem:Error:" + e.Message);
}
}
private void UpdateWorkOrder(int workOrderID, string woNumber, string woType, string paymentMethod, double?version, string rush, string rushReason,
string remarks, DateTime?issueDate, DateTime?dueDate, string cloneType, enterprise.Work_Order__c preWONumber, string siteCheckPurpose, string siteCheckPurposeAsOther, string sfWorkOrderID, double?amount, double?reviseWOCount, enterprise.User accountExecutive)
{
try
{
var workOrder = _db.Sales_JobMasterList_WO.Where(x => x.woID == workOrderID).FirstOrDefault();
if (workOrder != null)
{
switch (woType)
{
case "Production":
workOrder.woType = 10;
break;
case "Service":
workOrder.woType = 20;
break;
case "Site Check":
workOrder.woType = 30;
break;
default:
break;
}
switch (paymentMethod)
{
case "C.O.D (Invoice Attached)":
workOrder.PayMethods = 10;
break;
case "Invoice Mail Out By Office":
workOrder.PayMethods = 20;
break;
case "Installers Give Invoice To Client":
workOrder.PayMethods = 30;
break;
case "No Charge-Other":
workOrder.PayMethods = 40;
break;
case "No Charge-Mistakes":
workOrder.PayMethods = 41;
break;
case "No Charge-Under Warranty":
workOrder.PayMethods = 42;
break;
case "No Charge-Company Give Out As Gift":
workOrder.PayMethods = 43;
break;
case "No Charge-Sample":
workOrder.PayMethods = 44;
break;
case "No Charge-For Company Internal Use":
workOrder.PayMethods = 45;
break;
case "Decide By Installer":
workOrder.PayMethods = 46;
break;
case "PJ":
workOrder.PayMethods = 50;
break;
default:
break;
}
if (version != null)
{
workOrder.woRev = Convert.ToByte(version);
}
switch (rush)
{
case "Yes":
workOrder.rush = true;
workOrder.rushReason = rushReason;
break;
case "No":
workOrder.rush = false;
break;
default:
break;
}
workOrder.Remarks = remarks;
if (issueDate != null)
{
workOrder.issuedDate = (DateTime)issueDate;
}
if (dueDate != null)
{
workOrder.DeadLine = (DateTime)dueDate;
}
if (amount != null)
{
workOrder.WorkorderAmount = Convert.ToDouble(amount);
}
if (accountExecutive != null)
{
FsEmployee poEmployee = new FsEmployee(accountExecutive.CommunityNickname);
if (poEmployee.EmployeeNumber > 0)
{
workOrder.Sales = poEmployee.EmployeeNumber;
}
}
switch (cloneType)
{
case "Redo":
workOrder.rush = true;
workOrder.reDo = true;
if (reviseWOCount != null)
{
if (reviseWOCount > 0)
{
workOrder.revise = true;
workOrder.reviseVer = Convert.ToInt16(reviseWOCount);
}
else
{
workOrder.revise = false;
workOrder.reviseVer = null;
}
}
else
{
workOrder.revise = false;
workOrder.reviseVer = null;
}
workOrder.RedoOfWoNumbers = preWONumber != null ? preWONumber.Work_Order_Number__c : "";
//workOrder.WorkorderNumber = woNumber;
if (version != null)
{
workOrder.redoVer = Convert.ToInt16(version);
}
break;
case "Revise":
workOrder.rush = true;
workOrder.revise = true;
workOrder.reDo = false;
workOrder.redoVer = null;
workOrder.RedoOfWoNumbers = preWONumber != null ? preWONumber.Work_Order_Number__c : "";
//workOrder.WorkorderNumber = preWONumber != null ? preWONumber.Name : "";
if (version != null)
{
workOrder.reviseVer = Convert.ToInt16(version);
}
break;
case "New":
workOrder.revise = false;
workOrder.reviseVer = null;
workOrder.reDo = false;
workOrder.redoVer = null;
workOrder.RedoOfWoNumbers = "";
//workOrder.WorkorderNumber = woNumber;
break;
default:
break;
}
workOrder.WorkorderNumber = woNumber;
_db.Entry(workOrder).State = EntityState.Modified;
_db.SaveChanges();
}
if (woType == "Site Check")
{
int siteCheckID = CommonMethods.GetMISID(TableName.WO_Sitecheck_Purpose, sfWorkOrderID, salesForceProjectID);
if (siteCheckID == 0)
{
InsertNewSiteCheckPurpose(workOrderID, siteCheckPurpose, siteCheckPurposeAsOther);
int newId = SqlCommon.GetNewlyInsertedRecordID(TableName.WO_Sitecheck_Purpose);
if (newId > 0)
{
CommonMethods.InsertToMISSalesForceMapping(TableName.WO_Sitecheck_Purpose, sfWorkOrderID, newId.ToString(), salesForceProjectID);
}
}
else
{
UpdateSiteCheckPurpose(siteCheckID, siteCheckPurpose, siteCheckPurposeAsOther);
}
}
}
catch (Exception e)
{
LogMethods.Log.Error("UpdateWorkOrder:Error:" + e.Message);
}
}
public int GetNewID()
{
const string tblName = "Sales_JobMasterList_QuoteRev";
return(SqlCommon.GetNewlyInsertedRecordID(tblName));
}