public static bool CheckIsFinishedCP(Model.mdlParam json)
{
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date
},
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtIsFinishedCP = Manager.DataFacade.DTSQLCommand(@"SELECT CallPlanID
FROM CallPlan
WHERE EmployeeID = @EmployeeID and BranchID = @BranchID and IsFinish = 0 and Date >= @Date and Date < DATEADD(day,1,@Date) ", sp);
bool lCheck = true;
if (dtIsFinishedCP.Rows.Count == 0)
{
lCheck = false;
}
return(lCheck);
//klau false berarti datanya belum ada
}
public static List <Model.mdlMobileConfig> LoadMobileConfig(Model.mdlParam json)
{
var lmdlMobileConfigList = new List <Model.mdlMobileConfig>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtMobileConfig = Manager.DataFacade.DTSQLCommand(@"SELECT [BranchId]
,[ID]
,[Desc]
,[Value] FROM MobileConfig WHERE BranchID = @BranchID", sp); //003
foreach (DataRow drMobileConfig in dtMobileConfig.Rows)
{
var lmdlMobileConfig = new Model.mdlMobileConfig();
lmdlMobileConfig.BranchId = drMobileConfig["BranchId"].ToString();
lmdlMobileConfig.ID = drMobileConfig["ID"].ToString();
lmdlMobileConfig.Desc = drMobileConfig["Desc"].ToString();
lmdlMobileConfig.Value = drMobileConfig["Value"].ToString();
lmdlMobileConfigList.Add(lmdlMobileConfig);
}
return(lmdlMobileConfigList);
}
//christopher
public static Model.mdlDailyMsgList GetDailyMessage(Model.mdlParam param)
{
var mdlDailyMsgList = new Model.mdlDailyMsgList();
mdlDailyMsgList.DailyMessageList = DailyMessageFacade.LoadDailyMessage2(param);
return(mdlDailyMsgList);
}
public static List <Model.mdlDailyMsg> LoadDailyMessage2(Model.mdlParam json)
{
var mdlDailyMsgList = new List <Model.mdlDailyMsg>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date
},
//new SqlParameter() {ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date.AddDays(1) },
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"
SELECT
a.MessageID,
a.MessageName,
a.MessageDesc,
CONCAT('//img//DailyMessage//', c.ImageBase64) as MessageImg,
a.CreatedBy,
a.Date
FROM (
SELECT DISTINCT
a.MessageID
FROM DailyMessage a
INNER JOIN DailyMessageDetail b ON a.MessageID = b.MessageID
WHERE a.Date <= @Date AND a.EndDate >= @Date AND b.BranchID = @BranchID AND b.EmployeeID = @EmployeeID
) AS qr
INNER JOIN DailyMessage a ON a.MessageID = qr.MessageID
LEFT JOIN CustomerImage c ON c.ImageID = a.MessageImg", sp);
foreach (DataRow row in dtDailyMsg.Rows)
{
var mdlDailyMsg = new Model.mdlDailyMsg();
mdlDailyMsg.MessageID = row["MessageID"].ToString();
mdlDailyMsg.MessageName = row["MessageName"].ToString();
mdlDailyMsg.MessageDesc = row["MessageDesc"].ToString();
mdlDailyMsg.MessageImg = row["MessageImg"].ToString();
mdlDailyMsg.BranchID = "";
mdlDailyMsg.EndDate = "";
mdlDailyMsg.Date = Convert.ToDateTime(row["Date"]).ToString("yyyy-MM-dd hh:mm:ss");
mdlDailyMsg.CreatedBy = row["CreatedBy"].ToString();
mdlDailyMsgList.Add(mdlDailyMsg);
}
return(mdlDailyMsgList);
}
public static Model.mdlResult CheckVersion(Model.mdlParam param)
{
var lMobileConfig = DataContext.MobileConfigs.FirstOrDefault(fld => fld.BranchId.Equals(param.BranchID) && fld.ID.Equals("VERSION"));
var mdlResult = new Model.mdlResult();
mdlResult.Result = lMobileConfig.Value;
return(mdlResult);
}
public static List <Model.mdlCustomer> LoadCustomer(Model.mdlParam json)
{
var mdlCustomerList = new List <Model.mdlCustomer>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date
},
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtCustomer = Manager.DataFacade.DTSQLCommand(@"SELECT * FROM Customer", sp);
foreach (DataRow drCustomer in dtCustomer.Rows)
{
var mdlCustomer = new Model.mdlCustomer();
mdlCustomer.CustomerID = drCustomer["CustomerID"].ToString();
mdlCustomer.CustomerName = drCustomer["CustomerName"].ToString();
mdlCustomer.CustomerAddress = drCustomer["CustomerAddress"].ToString();
mdlCustomer.Phone = drCustomer["Phone"].ToString();
mdlCustomer.Email = drCustomer["Email"].ToString();
mdlCustomer.PIC = drCustomer["PIC"].ToString();
mdlCustomer.CustomerTypeID = drCustomer["CustomerTypeID"].ToString();
mdlCustomer.Latitude = drCustomer["Latitude"].ToString();
mdlCustomer.Longitude = drCustomer["Longitude"].ToString();
mdlCustomer.BranchID = drCustomer["BranchID"].ToString();
mdlCustomer.Radius = drCustomer["Radius"].ToString();
mdlCustomer.City = drCustomer["City"].ToString();
mdlCustomer.CountryRegionCode = drCustomer["CountryRegionCode"].ToString();
//mdlCustomer.Blocked = drCustomer["Blocked"].ToString();
mdlCustomer.Account = drCustomer["Account"].ToString();
mdlCustomer.Channel = drCustomer["Channel"].ToString();
mdlCustomer.Distributor = drCustomer["Distributor"].ToString();
mdlCustomer.EmployeeID = drCustomer["EmployeeID"].ToString();
mdlCustomer.Mobilephone1 = drCustomer["Mobilephone1"].ToString();
mdlCustomer.Mobilephone2 = drCustomer["Mobilephone2"].ToString();
mdlCustomer.BankAccountNumber = drCustomer["BankAccountNumber"].ToString();
mdlCustomerList.Add(mdlCustomer);
}
return(mdlCustomerList);
}
public static List <Model.mdlReturOrder> LoadReturOrder(Model.mdlParam json)
{
var mdlReturOrderList = new List <Model.mdlReturOrder>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@DateStart", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date
},
//new SqlParameter() {ParameterName = "@DateFinish", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.AddDays(1).Date },
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtReturOrder = Manager.DataFacade.DTSQLCommand("SELECT ReturNumber,CustomerID,EmployeeID,BranchID,ReturDate,ReturStatus,Description,Signature,IsPrint,Remark,ReceivedDate FROM ReturOrder WHERE EmployeeID = @EmployeeID AND BranchID = @BranchID AND ReturDate <= @DateStart AND ReturStatus = '' AND CONVERT(varchar, Signature) = '' AND Remark = ''", sp);
foreach (DataRow row in dtReturOrder.Rows)
{
var mdlReturOrder = new Model.mdlReturOrder();
mdlReturOrder.ReturNumber = row["ReturNumber"].ToString();
mdlReturOrder.CustomerID = row["CustomerID"].ToString();
mdlReturOrder.EmployeeID = row["EmployeeID"].ToString();
mdlReturOrder.BranchID = row["BranchID"].ToString();
mdlReturOrder.ReturDate = Convert.ToDateTime(row["ReturDate"]).ToString("yyyy-MM-dd hh:mm:ss");
mdlReturOrder.ReturStatus = row["ReturStatus"].ToString();
mdlReturOrder.Description = row["Description"].ToString();
mdlReturOrder.Signature = row["Signature"].ToString();
mdlReturOrder.IsPrint = row["IsPrint"].ToString();
mdlReturOrder.Remark = row["Remark"].ToString();
mdlReturOrder.ReceivedDate = Convert.ToDateTime(row["ReceivedDate"]).ToString("yyyy-MM-dd hh:mm:ss");
mdlReturOrderList.Add(mdlReturOrder);
}
return(mdlReturOrderList);
}
public static List <Model.mdlCompetitorProduct> LoadCompetitorProduct(Model.mdlParam param, List <Model.mdlCompetitor> listCompetitor)
{
List <SqlParameter> sp = new List <SqlParameter>();
StringBuilder sb = new StringBuilder();
int count = 1;
foreach (var com in listCompetitor)
{
var sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "@ComID" + count.ToString();
if (com == listCompetitor.Last())
{
sb.Append("@ComID" + count.ToString());
}
else
{
sb.Append("@ComID" + count.ToString() + ",");
}
sqlParameter.SqlDbType = SqlDbType.NVarChar;
sqlParameter.Value = com.CompetitorID;
sp.Add(sqlParameter);
count++;
}
var listCompetitorProduct = new List <Model.mdlCompetitorProduct>();
string sql = @"SELECT CompetitorID,CompetitorProductID,CompetitorProductName FROM CompetitorProduct WHERE CompetitorID IN (" + sb.ToString() + ")";
DataTable dt = DataFacade.DTSQLCommand(sql, sp);
foreach (DataRow row in dt.Rows)
{
var model = new Model.mdlCompetitorProduct();
model.CompetitorID = row["CompetitorID"].ToString();
model.CompetitorProductID = row["CompetitorProductID"].ToString();
model.CompetitorProductName = row["CompetitorProductName"].ToString();
listCompetitorProduct.Add(model);
}
return(listCompetitorProduct);
}
public static List <Model.mdlCompetitor> LoadCompetitor(Model.mdlParam param)
{
var listCompetitor = new List <Model.mdlCompetitor>();
List <SqlParameter> sp = new List <SqlParameter>()
{
};
string sql = "SELECT CompetitorID,CompetitorName FROM Competitor";
DataTable dt = DataFacade.DTSQLCommand(sql, sp);
foreach (DataRow row in dt.Rows)
{
var model = new Model.mdlCompetitor();
model.CompetitorID = row["CompetitorID"].ToString();
model.CompetitorName = row["CompetitorName"].ToString();
listCompetitor.Add(model);
}
return(listCompetitor);
}
public static List <Model.mdlProduct> LoadProduct(Model.mdlParam json)
{
var mdlProductList = new List <Model.mdlProduct>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date
},
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtProduct = Manager.DataFacade.DTSQLCommand(@"SELECT ProductID,ProductName,ProductWeight,UOM,DNR_Code,SAP_Code,Price FROM Product", sp);
foreach (DataRow row in dtProduct.Rows)
{
var mdlProduct = new Model.mdlProduct();
mdlProduct.ProductID = row["ProductID"].ToString();
mdlProduct.ProductName = row["ProductName"].ToString();
mdlProduct.ProductType = "";
mdlProduct.ProductGroup = "";
mdlProduct.ProductWeight = row["ProductWeight"].ToString();
mdlProduct.UOM = row["UOM"].ToString();
mdlProduct.ArticleNumber = "";
mdlProductList.Add(mdlProduct);
}
//var mdlProductListnew = new Model.mdlProductList();
//mdlProductListnew.ProductList = mdlProductList;
return(mdlProductList);
}
public static List <Model.mdlCompetitorActivity> LoadCompetitorActivity(Model.mdlParam param)
{
var listCompetitorActivity = new List <Model.mdlCompetitorActivity>();
List <SqlParameter> sp = new List <SqlParameter>()
{
};
string sql = "SELECT [ActivityID],[CompetitorID],[ActivityName] FROM [CompetitorActivity]";
DataTable dt = DataFacade.DTSQLCommand(sql, sp);
foreach (DataRow row in dt.Rows)
{
var model = new Model.mdlCompetitorActivity();
model.ActivityID = row["ActivityID"].ToString();
model.CompetitorID = row["CompetitorID"].ToString();
model.ActivityName = row["ActivityName"].ToString();
listCompetitorActivity.Add(model);
}
return(listCompetitorActivity);
}
public static List <Model.mdlDailyMsg> LoadDailyMessage(Model.mdlParam json)
{
var mdlDailyMsgList = new List <Model.mdlDailyMsg>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"SELECT MessageID
,MessageName
,MessageDesc
,MessageImg
,CreatedBy
,Date
From DailyMessage WHERE Date <= @Date AND EndDate >= @Date AND BranchID = @BranchID", sp);
foreach (DataRow row in dtDailyMsg.Rows)
{
var mdlDailyMsg = new Model.mdlDailyMsg();
mdlDailyMsg.MessageID = row["MessageID"].ToString();
mdlDailyMsg.MessageName = row["MessageName"].ToString();
mdlDailyMsg.MessageDesc = row["MessageDesc"].ToString();
mdlDailyMsg.MessageImg = row["MessageImg"].ToString();
mdlDailyMsg.Date = Convert.ToDateTime(row["Date"]).ToString("yyyy-MM-dd");
mdlDailyMsg.CreatedBy = row["CreatedBy"].ToString();
mdlDailyMsgList.Add(mdlDailyMsg);
}
return(mdlDailyMsgList);
}
public static List <Model.mdlReturOrderDetail> LoadReturOrderDetail(Model.mdlParam json)
{
var mdlReturOrderDetailList = new List <Model.mdlReturOrderDetail>();
var returOrder = LoadReturOrder(json).ToList();
var returOrderDetail = new List <Model.mdlReturOrderDetail>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@DateStart", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date
},
//new SqlParameter() {ParameterName = "@DateFinish", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.AddDays(1).Date },
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtDetailOrderDetail = Manager.DataFacade.DTSQLCommand("SELECT ReturNumber,ProductID,UOM,Quantity,QuantityReal,ArticleNumber FROM ReturOrderDetail WHERE ReturNumber IN (SELECT ReturNumber FROM ReturOrder WHERE EmployeeID = @EmployeeID AND BranchID = @BranchID AND ReturDate <= @DateStart AND ReturStatus = '' AND CONVERT(varchar, Signature) = '' AND Remark = '')", sp);
foreach (DataRow row in dtDetailOrderDetail.Rows)
{
var mdlReturDetail = new Model.mdlReturOrderDetail();
mdlReturDetail.ReturNumber = row["ReturNumber"].ToString();
mdlReturDetail.ProductID = row["ProductID"].ToString();
mdlReturDetail.UOM = row["UOM"].ToString();
mdlReturDetail.Quantity = row["Quantity"].ToString();
mdlReturDetail.QuantityReal = row["QuantityReal"].ToString();
mdlReturDetail.ArticleNumber = row["ArticleNumber"].ToString();
mdlReturOrderDetailList.Add(mdlReturDetail);
}
return(mdlReturOrderDetailList);
}
public static List <Model.mdlQuestion> LoadQuestion(Model.mdlParam json)
{
var listQuestion = new List <Model.mdlQuestion>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@RoleID", SqlDbType = SqlDbType.VarChar, Value = json.Role
}
};
string sql = @"SELECT a.QuestionID, a.QuestionText, a.AnswerTypeID, a.IsSubQuestion, a.Sequence, a.QuestionSetID, a.QuestionCategoryID,
a.AnswerID, a.No, a.Mandatory, a.IsActive
FROM Question a
INNER JOIN Question_Role b ON b.QuestionID = a.QuestionID
WHERE b.RoleID = @RoleID";
DataTable dt = DataFacade.DTSQLCommand(sql, sp);
foreach (DataRow row in dt.Rows)
{
var model = new Model.mdlQuestion();
model.QuestionID = row["QuestionID"].ToString();
model.QuestionText = row["QuestionText"].ToString();
model.AnswerTypeID = row["AnswerTypeID"].ToString();
model.IsSubQuestion = Convert.ToBoolean(row["IsSubQuestion"].ToString());
model.Sequence = Convert.ToInt32(row["Sequence"].ToString());
model.QuestionSetID = row["QuestionSetID"].ToString();
model.QuestionCategoryID = row["QuestionCategoryID"].ToString();
model.AnswerID = row["AnswerID"].ToString();
model.No = row["No"].ToString();
model.Mandatory = Convert.ToBoolean(row["Mandatory"].ToString());
model.IsActive = Convert.ToBoolean(row["IsActive"].ToString());
listQuestion.Add(model);
}
return(listQuestion);
}
public static List <Model.mdlSisaStockType> LoadSisaStockType(Model.mdlParam param)
{
var listSisaStockType = new List <Model.mdlSisaStockType>();
List <SqlParameter> sp = new List <SqlParameter>()
{
};
string sql = "SELECT SisaStockTypeID, Description, Category, Seq FROM SisaStockType";
DataTable dt = DataFacade.DTSQLCommand(sql, sp);
foreach (DataRow row in dt.Rows)
{
var model = new Model.mdlSisaStockType();
model.SisaStockTypeID = row["SisaStockTypeID"].ToString();
model.Description = row["Description"].ToString();
model.Category = row["Category"].ToString();
model.Seq = row["Seq"].ToString();
listSisaStockType.Add(model);
}
return(listSisaStockType);
}
public static Model.mdlResultSV SetUserConfig(Core.Model.mdlSetDeviceIDParam param)
{
var mdlResult = new Model.mdlResultSV();
mdlResult.Title = "Set User Config";
var mdlUserConfig = new Model.mdlSetDeviceID();
mdlUserConfig = UserConfigFacade.SetUserConfig(param);
if (mdlUserConfig.EmployeeID == null || mdlUserConfig.Uid == "")
{
UserConfigFacade.UploadUid(param);
mdlUserConfig = UserConfigFacade.SetUserConfig(param);
}
if (mdlUserConfig.EmployeeID == null || mdlUserConfig.EmployeeID == "")
{
mdlUserConfig.EmployeeID = "";
mdlUserConfig.BranchID = "";
mdlUserConfig.BranchName = "";
mdlUserConfig.VehicleNumber = "";
mdlUserConfig.IpLocal = "";
mdlUserConfig.PortLocal = "";
mdlUserConfig.IpPublic = "";
mdlUserConfig.PortPublic = "";
mdlUserConfig.IpAlternative = "";
mdlUserConfig.PortAlternative = "";
mdlUserConfig.Password = "";
mdlUserConfig.EmployeeName = "";
mdlUserConfig.Version = "";
mdlResult.StatusCode = "00";
mdlResult.StatusMessage = "User Config Not Found";
mdlResult.Value = mdlUserConfig;
//string json = Services.RestPublisher.Serialize(mdlResult);
//var result = Services.RestPublisher.Deserialize<Model.mdlResultSV>(json);
//return result;
}
else
{
var mdlparamAndroidKey = new Model.mdlSaveAndroidKeyParam();
mdlparamAndroidKey.AndroidKey = param.token;
mdlparamAndroidKey.EmployeeID = mdlUserConfig.EmployeeID;
mdlparamAndroidKey.BranchID = mdlUserConfig.BranchID;
//var mobileConfig = MobileConfigFacade.GetMobileConfigbyID(mdlUserConfig.BranchID);
var paramNew = new Model.mdlParam();
paramNew.BranchID = mdlUserConfig.BranchID;
paramNew.EmployeeID = mdlUserConfig.EmployeeID;
var mobileConfig = MobileConfigFacade.LoadMobileConfig(paramNew);
var mobileConfigVersion = mobileConfig.FirstOrDefault(fld => fld.ID.Equals("VERSION"));
if (mobileConfigVersion != null)
{
mdlUserConfig.Version = mobileConfigVersion.Value;
}
//FERNANDES-PasswordResetUpdate
var mobileConfigPasswordReset = mobileConfig.FirstOrDefault(fld => fld.ID.Equals("PASSWORDRESET"));
if (mobileConfigPasswordReset != null)
{
mdlUserConfig.PasswordReset = mobileConfigPasswordReset.Value;
}
var employee = EmployeeFacade.GetEmployeeByID(mdlUserConfig.EmployeeID);
if (employee != null)
{
mdlUserConfig.EmployeeName = employee.EmployeeName;
}
string res = string.Empty;
var checkEmployee = AndroidKeyFacade.CheckEmployee(mdlparamAndroidKey);
if (checkEmployee.EmployeeID == null || checkEmployee.EmployeeID == "")
{
res = Manager.AndroidKeyFacade.InsertAndroidKey(mdlparamAndroidKey);
}
else
{
res = Manager.AndroidKeyFacade.UpdateAndroidKey(mdlparamAndroidKey);
}
mdlResult.StatusCode = "01";
mdlResult.StatusMessage = "Set User Config Success";
mdlResult.Value = mdlUserConfig;
}
string json = Services.RestPublisher.Serialize(mdlResult);
var result = Services.RestPublisher.Deserialize <Model.mdlResultSV>(json);
return(mdlResult);
}
//006 OPTIMAZE
public static Model.mdlJsonList LoadJson(Model.mdlParam json)
{
//001
var mdlJson = new Model.mdlJson();
var mdlJsonList = new List <Model.mdlJson>();
var mdlCallPlan = CallPlanFacade.LoadCallPlan(json);
var mdlCallPlanDetail = CallPlanFacade.LoadCallPlanDetail(json);
//--006
var mdlCustomerList = new List <Model.mdlCustomer>();
var mdlCustomerTypeList = new List <Model.mdlCustomerType>();
//mdlProductList = new List<Model.mdlProduct>(); //005 Close Code
//--002
var mdlCost = new List <Model.mdlCost>();
var mdlDeliveryOrderList = new List <Model.mdlDeliveryOrder>();
var mdlDeliveryOrderDetailList = new List <Model.mdlDeliveryOrderDetail>();
//002--
var mdlProductList = new List <Model.mdlProduct>();
var mdlDailyMsgList = new List <Model.mdlDailyMsg>(); //003
var mdlDailyCostList = new List <Model.mdlDailyCost>();
var mdlReturOrderList = new List <Model.mdlReturOrder>();
var mdlReturOrderDetailList = new List <Model.mdlReturOrderDetail>();
var mdlReasonList = new List <Model.mdlReason>();
var mdlProductUOMList = new List <Model.mdlProductUOM>(); //007
var mdlMobileConfigList = new List <Model.mdlMobileConfig>();
var mdlWarehouseList = new List <Model.mdlWarehouse>();
var mdlUnvalidDOProductList = new List <Model.mdlDeliveryOrderDetail>();
var mdlVisit = new List <Model.mdlDBVisit>();
var mdlVisitDetailList = new List <Model.mdlDBVisitDetail2>();
//Wyeth
var mdlPromoList = new List <Model.mdlPromo>();
var mdlCompetitorActivityList = new List <Model.mdlCompetitorActivity>();
var mdlCompetitor = new List <Model.mdlCompetitor>();
var mdlPhotoTypeList = new List <Model.mdlPhotoType>();
var mdlCompetitorProduct = new List <Model.mdlCompetitorProduct>();
var mdlMenuMobileList = new List <Model.mdlMenuMobile>();
var mdlPOSMProductList = new List <Model.mdlPOSM>();
var mdlSisaStockType = new List <Model.mdlSisaStockType>();
var mdlQuestion = new List <Model.mdlQuestion>();
var mdlAnswer = new List <Model.mdlAnswer>();
var mdlAnswerType = new List <Model.mdlAnswer_Type>();
var mdlQuestionSet = new List <Model.mdlQuestion_Set>();
var mdlQuestionCategory = new List <Model.mdlQuestion_Category>();
var mdlCompetitorActivityType = new List <Model.mdlCompetitorActivityType>();
var mdlDepositType = new List <Model.mdlDepositType>();
//if (mdlCallPlan.Count > 0) -- fernandes
//fernandes
foreach (var cp in mdlCallPlan)
{
if (cp.Result == "FAILED" || mdlCallPlanDetail.Count == 0)
{
mdlJson.Result = "Tidak ada call plan";
}
else if (cp.Result == "FINISH")
{
mdlJson.Result = "Call plan sudah selesai";
}
else
{
mdlCustomerList = CustomerFacade.LoadCustomer(json);
mdlCustomerTypeList = CustomerTypeFacade.LoadCustomerType();
//mdlDeliveryOrderList = DeliveryOrderFacade.LoadDeliveryOrder(json);
//mdlDeliveryOrderDetailList = DeliveryOrderFacade.LoadDeliveryOrderDetail(json, mdlDeliveryOrderList);
//mdlUnvalidDOProductList = DeliveryOrderFacade.UnvalidDOProduct(mdlDeliveryOrderList);
mdlProductList = ProductFacade.LoadProduct(json);
//var mdlProductList = ProductFacade.LoadProduct(); //005 close code
//mdlReturOrderList = ReturFacade.LoadReturOrder(json);
//mdlReturOrderDetailList = ReturFacade.LoadReturOrderDetail(json);
mdlCost = CostFacade.LoadCost(); //002
mdlReasonList = ReasonFacade.LoadReason();
//christopher
mdlDailyMsgList = DailyMessageFacade.LoadDailyMessage2(json); //003
//christopher
mdlProductUOMList = ProductFacade.LoadProductUOM(mdlDeliveryOrderDetailList); //007
//christopher
mdlMobileConfigList = MobileConfigFacade.LoadMobileConfig(json);
//christopher
mdlWarehouseList = WarehouseFacade.LoadWarehouse(mdlCallPlan);
mdlVisit = VisitFacade.LoadVisit(mdlCallPlan);
mdlVisitDetailList = VisitFacade.LoadVisitDetail(mdlVisit);
mdlDailyCostList = CostFacade.LoadDailyCost(mdlVisit);
//wyeth
mdlPromoList = PromoFacade.LoadPromo(json);
//mdlCompetitorActivityList = CompetitorFacade.LoadCompetitorActivity(json);
//mdlCompetitor = CompetitorFacade.LoadCompetitor(json);
//mdlCompetitorProduct = CompetitorFacade.LoadCompetitorProduct(json, mdlCompetitor);
mdlMenuMobileList = MenuFacade.LoadMenuMobile(json.Role);
mdlPOSMProductList = POSMFacade.LoadPOSMProduct();
mdlSisaStockType = SisaStockTypeFacade.LoadSisaStockType(json);
//mdlCompetitorActivityType = CompetitorActivityTypeFacade.LoadCompetitorActivityType(json);
mdlPhotoTypeList = PhotoTypeFacade.LoadPhotoType();
mdlQuestion = QuestionFacade.LoadQuestion(json);
//mdlAnswer = AnswerFacade.LoadAnswer();
//mdlAnswerType = AnswerFacade.LoadAnswerType();
//mdlQuestionSet = QuestionFacade.LoadQuestionSet();
//mdlQuestionCategory = QuestionFacade.LoadQuestionCategory();
mdlDepositType = DepositTypeFacade.LoadDepositType();
mdlJson.Result = "Unduh berhasil";
}
}
//006--
//var mdlVisitList = VisitFacade.LoadVisit(json);
//var mdlVisitDetailList = VisitFacade.LoadVisitDetail(json);
mdlJson.CustomerList = mdlCustomerList;
mdlJson.CustomerTypeList = mdlCustomerTypeList;
mdlJson.DeliveryOrderList = mdlDeliveryOrderList;
mdlJson.DeliveryOrderDetailList = mdlDeliveryOrderDetailList;
mdlJson.ProductList = mdlProductList;
//mdlJson.ProductList = mdlProductList; //05 Close Code
mdlJson.ReturOrderList = mdlReturOrderList;
mdlJson.ReturOrderDetailList = mdlReturOrderDetailList;
mdlJson.CallPlanList = mdlCallPlan;
mdlJson.CallPlanDetailList = mdlCallPlanDetail;
mdlJson.CostList = mdlCost; //002
mdlJson.DailyMessageList = mdlDailyMsgList; //003
mdlJson.ReasonList = mdlReasonList;
mdlJson.ProductUOMList = mdlProductUOMList; //007
//christopher
mdlJson.MobileConfigList = mdlMobileConfigList;
mdlJson.WarehouseList = mdlWarehouseList;
mdlJson.UnvalidDOProductList = mdlUnvalidDOProductList;
mdlJson.Visit = mdlVisit;
mdlJson.VisitDetailList = mdlVisitDetailList;
mdlJson.DailyCostList = mdlDailyCostList;
//wyeth
mdlJson.PromoList = mdlPromoList;
//mdlJson.CompetitorActivityList = mdlCompetitorActivityList;
mdlJson.CompetitorList = mdlCompetitor;
mdlJson.SisaStockTypeList = mdlSisaStockType;
mdlJson.CompetitorActivityTypeList = mdlCompetitorActivityType;
mdlJson.CompetitorProductList = mdlCompetitorProduct;
mdlJson.MenuMobileList = mdlMenuMobileList;
mdlJson.POSMProductList = mdlPOSMProductList;
mdlJson.PhotoTypeList = mdlPhotoTypeList;
mdlJson.QuestionList = mdlQuestion;
mdlJson.AnswerList = mdlAnswer;
mdlJson.AnswerTypeList = mdlAnswerType;
mdlJson.QuestionSetList = mdlQuestionSet;
mdlJson.QuestionCategoryList = mdlQuestionCategory;
mdlJson.DepositTypeList = mdlDepositType;
//mdlJson.VisitList = mdlVisitList;
//mdlJson.VisitDetailList = mdlVisitDetailList;
var mdlJsonListNew = new Model.mdlJsonList();
mdlJsonList.Add(mdlJson);
mdlJsonListNew.mdlJson = mdlJsonList;
return(mdlJsonListNew);
//001
}
public static List <Model.mdlCallPlanDetail> LoadCallPlanDetail(Model.mdlParam json)
{
var mdlCallPlanDetailList = new List <Model.mdlCallPlanDetail>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date
},
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtcallplandetail = Manager.DataFacade.DTSQLCommand(@"SELECT
CallPlanID
,a.CustomerID
,Sequence
,WarehouseID
,[Time]
FROM CallPlanDetail a
inner join customer b on a.CustomerID=b.CustomerID
WHERE CallPlanID IN (SELECT CallPlanID FROM CallPlan WHERE EmployeeID = @EmployeeID and BranchID = @BranchID and IsFinish = 0 and Date >= @Date and Date < DATEADD(day,1,@Date))", sp); //006
foreach (DataRow row in dtcallplandetail.Rows)
{
var mdlCallPlanDetail = new Model.mdlCallPlanDetail();
mdlCallPlanDetail.CallPlanID = row["CallPlanID"].ToString();
//mdlCallPlanDetail.CPDetailID = row["CPDetailID"].ToString();
DateTime time = DateTime.ParseExact(row["Time"].ToString(), "HH:mm:ss", CultureInfo.InvariantCulture);
mdlCallPlanDetail.Time = time.ToString("HH:mm:ss");
mdlCallPlanDetail.CPDetailID = "";
mdlCallPlanDetail.CustomerID = row["CustomerID"].ToString();
mdlCallPlanDetail.Sequence = row["Sequence"].ToString();
mdlCallPlanDetail.WarehouseID = row["WarehouseID"].ToString();
var week = VisitFacade.GetIso8601WeekOfYear(Convert.ToDateTime(json.Date));
List <SqlParameter> spCheck = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Week", SqlDbType = SqlDbType.Int, Value = week
},
new SqlParameter()
{
ParameterName = "@CustomerID", SqlDbType = SqlDbType.NVarChar, Value = mdlCallPlanDetail.CustomerID
}
};
DataTable dtCheckVisit = Manager.DataFacade.DTSQLCommand(@"select a.VisitID,CustomerID from Visit a
inner join VisitDetail b on a.VisitID = b.VisitID where b.isDeliver=1 and b.CustomerID= @CustomerID and a.VisitWeek= @Week ", spCheck);
if (dtCheckVisit.Rows.Count > 0)
{
mdlCallPlanDetail.BPStock = true;
}
else
{
mdlCallPlanDetail.BPStock = false;
}
List <SqlParameter> sp3 = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@CallPlanID", SqlDbType = SqlDbType.NVarChar, Value = mdlCallPlanDetail.CallPlanID
},
new SqlParameter()
{
ParameterName = "@CustomerID", SqlDbType = SqlDbType.NVarChar, Value = mdlCallPlanDetail.CustomerID
}
};
Manager.DataFacade.DTSQLVoidCommand(@"update callplandetail set isDownload = 1 where CallPlanID = @CallPlanID AND CustomerID = @CustomerID", sp3);
mdlCallPlanDetailList.Add(mdlCallPlanDetail);
}
return(mdlCallPlanDetailList);
}
public static List <Model.mdlCallPlan> LoadCallPlan(Model.mdlParam json)
{
var mdlcallplanList = new List <Model.mdlCallPlan>();
bool Check = CheckExistingCP(json);
if (Check == false)
{
bool CheckFinishedCP = CheckIsFinishedCP(json);
if (CheckFinishedCP == false)
{
var mdlcallplan = new Model.mdlCallPlan();
mdlcallplan.Result = "FINISH";
mdlcallplanList.Add(mdlcallplan);
}
else
{
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date
},
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtcallplan = Manager.DataFacade.DTSQLCommand(@"SELECT CallPlanID
,a.EmployeeID
,a.BranchID
,Date
,VehicleID
,b.EmployeeName Helper1
,c.EmployeeName Helper2
FROM CallPlan a
left join Employee b on a.Helper1 = b.EmployeeID
left join Employee c on c.EmployeeID=a.Helper2
WHERE a.EmployeeID = @EmployeeID and a.BranchID = @BranchID and IsFinish = 0 and Date >= @Date and Date < DATEADD(day,1,@Date) ", sp);
foreach (DataRow row in dtcallplan.Rows)
{
var mdlcallplan = new Model.mdlCallPlan();
mdlcallplan.CallPlanID = row["CallPlanID"].ToString();
mdlcallplan.EmployeeID = row["EmployeeID"].ToString();
mdlcallplan.BranchID = row["BranchID"].ToString();
mdlcallplan.Date = Convert.ToDateTime(row["Date"]).ToString("yyyy-MM-dd hh:mm:ss");
mdlcallplan.VehicleID = row["VehicleID"].ToString();
mdlcallplan.Helper1 = row["Helper1"].ToString();
mdlcallplan.Helper2 = row["Helper2"].ToString();
mdlcallplan.Result = "SUCCESS";
List <SqlParameter> sp3 = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@CallPlanID", SqlDbType = SqlDbType.NVarChar, Value = mdlcallplan.CallPlanID
}
};
Manager.DataFacade.DTSQLVoidCommand(@"update callplan set isDownload = 1 where callplanid=@CallPlanID", sp3);
List <SqlParameter> sp2 = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@VehicleID", SqlDbType = SqlDbType.NVarChar, Value = mdlcallplan.VehicleID
}
};
DataTable dtVisit = Manager.DataFacade.DTSQLCommand(@"select top 1 VisitDate,KMFinish from Visit where VehicleID=@VehicleID
and visitdate < GETDATE()
order by VisitDate desc", sp2);
if (dtVisit.Rows.Count > 0)
{
mdlcallplan.KMAkhir = dtVisit.Rows[0]["KMFinish"].ToString();
}
mdlcallplanList.Add(mdlcallplan);
}
}
}
else
{
var mdlcallplan = new Model.mdlCallPlan();
mdlcallplan.Result = "FAILED";
mdlcallplanList.Add(mdlcallplan);
}
return(mdlcallplanList);
}
//001
//public static List<Model.DeliveryOrder> GetDOByCustomerID(string customerID,string warehouseID)
//{
// var DO = DataContext.DeliveryOrders.Where(fld => fld.CustomerID.Equals(customerID) && fld.WarehouseID.Equals(warehouseID)).OrderByDescending(fld => fld.DODate).ToList();
// return DO;
//}
public static List <Model.mdlDeliveryOrder> LoadDeliveryOrder(Model.mdlParam json)
{
var mdlDeliveryOrderList = new List <Model.mdlDeliveryOrder>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@StartDate", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date
},
new SqlParameter()
{
ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date.AddDays(1)
},
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtDeliveryOrder = DataFacade.DTSQLCommand(@"SELECT a.DONumber
,a.CallPlanID
,a.DODate
,a.DOStatus
,a.Description
,a.CustomerID
,a.EmployeeID
,a.VehicleID
,a.BranchID
,a.Signature
,a.IsPrint
,a.Remark
,a.WarehouseID
FROM DeliveryOrder a
INNER JOIN (
SELECT CallPlanID FROM CallPlan
WHERE EmployeeID = @EmployeeID AND BranchID = @BranchID AND IsFinish = 0 AND Date >= @StartDate AND Date < @FinishDate
) b ON b.CallPlanID = a.CallPlanID
WHERE DOStatus = 'Shipper'", sp);
//INNER JOIN (select * from CallPlanDetail where CallPlanID='C1000001') b ON b.CustomerID = a.CustomerID
foreach (DataRow row in dtDeliveryOrder.Rows)
{
var mdlDeliveryOrder = new Model.mdlDeliveryOrder();
mdlDeliveryOrder.DONumber = row["DONumber"].ToString();
mdlDeliveryOrder.CallPlanID = row["CallPlanID"].ToString();
mdlDeliveryOrder.CustomerID = row["CustomerID"].ToString();
mdlDeliveryOrder.WarehouseID = row["WarehouseID"].ToString();
mdlDeliveryOrder.EmployeeID = row["EmployeeID"].ToString();
mdlDeliveryOrder.VehicleID = row["VehicleID"].ToString(); //006--
mdlDeliveryOrder.DODate = Convert.ToDateTime(row["DODate"]).ToString("yyyy-MM-dd hh:mm:ss");
mdlDeliveryOrder.DOStatus = row["DOStatus"].ToString();
mdlDeliveryOrder.Description = row["Description"].ToString();
mdlDeliveryOrder.Signature = row["Signature"].ToString();
mdlDeliveryOrder.IsPrint = row["IsPrint"].ToString();
mdlDeliveryOrder.Remark = row["Remark"].ToString();
mdlDeliveryOrder.BranchID = row["BranchID"].ToString();
mdlDeliveryOrder.VisitID = "";
mdlDeliveryOrderList.Add(mdlDeliveryOrder);
}
return(mdlDeliveryOrderList);
}
public static List <Model.mdlDeliveryOrderDetail> LoadDeliveryOrderDetail(Model.mdlParam json, List <Model.mdlDeliveryOrder> listDO)
{
List <SqlParameter> sp = new List <SqlParameter>();
StringBuilder sb = new StringBuilder();
int count = 1;
foreach (var DO in listDO)
{
var sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "@DoNumbers" + count.ToString();
if (DO == listDO.Last())
{
sb.Append("@DoNumbers" + count.ToString());
}
else
{
sb.Append("@DoNumbers" + count.ToString() + ",");
}
sqlParameter.SqlDbType = SqlDbType.NVarChar;
sqlParameter.Value = DO.DONumber;
sp.Add(sqlParameter);
count++;
}
var mdlDeliveryOrderDetailList = new List <Model.mdlDeliveryOrderDetail>();
var deliveryOrder = LoadDeliveryOrder(json);
//List<SqlParameter> sp = new List<SqlParameter>()
//{
// new SqlParameter() {ParameterName = "@StartDate", SqlDbType = SqlDbType.DateTime, Value= DateTime.Now.Date },
// new SqlParameter() {ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value= DateTime.Now.Date.AddDays(1) },
// new SqlParameter() {ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID },
// new SqlParameter() {ParameterName = "@DoNumbers", SqlDbType = SqlDbType.NVarChar, Value = DONumbers},
// new SqlParameter() {ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID }
//};
// DataTable dtDeliveryOrderDetailList = DataFacade.DTSQLCommand(@"SELECT DONumber,
// ProductID,
// UOM,
// Quantity,
// QuantityReal,
// ProductGroup,
// LotNumber,
// BoxID
// FROM DeliveryOrderDetail
// WHERE DONumber IN (
// SELECT a.DONumber
// FROM DeliveryOrder a
// INNER JOIN (
// SELECT * FROM CallPlanDetail
// WHERE CallPlanID IN (
// SELECT CallPlanID FROM CallPlan
// WHERE EmployeeID = @EmployeeID AND BranchID = @BranchID AND IsFinish = 0 AND Date >= @StartDate
// )
// ) b ON b.CustomerID = a.CustomerID
// WHERE EmployeeID = @EmployeeID AND BranchID = @BranchID AND DODate >= @StartDate AND DOStatus = 'Shipper'
// )", sp);
DataTable dtDeliveryOrderDetailList = DataFacade.DTSQLCommand(@"SELECT DONumber,
ProductID,
UOM,
Quantity,
QuantityReal,
ProductGroup,
LotNumber,
BoxID,
Line
FROM DeliveryOrderDetail WHERE DONumber IN (" + sb.ToString() + ") AND Quantity != 0", sp);
foreach (DataRow row in dtDeliveryOrderDetailList.Rows)
{
var mdlDeliveryOrderDetail = new Model.mdlDeliveryOrderDetail();
mdlDeliveryOrderDetail.DONumber = row["DONumber"].ToString();
mdlDeliveryOrderDetail.ProductID = row["ProductID"].ToString();
mdlDeliveryOrderDetail.UOM = row["UOM"].ToString();
mdlDeliveryOrderDetail.Quantity = row["Quantity"].ToString();
mdlDeliveryOrderDetail.QuantityReal = row["QuantityReal"].ToString();
mdlDeliveryOrderDetail.ProductGroup = row["ProductGroup"].ToString();
mdlDeliveryOrderDetail.LotNumber = row["LotNumber"].ToString();
mdlDeliveryOrderDetail.BoxID = row["BoxID"].ToString();
mdlDeliveryOrderDetail.Line = row["Line"].ToString();
mdlDeliveryOrderDetailList.Add(mdlDeliveryOrderDetail);
}
return(mdlDeliveryOrderDetailList);
}
