public void Cost_Delete_ById(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
int No = SafeValue.SafeInt(job["No"], 0);
bool status = false;
//===========log
C2.CtmJobEventLog lg = new C2.CtmJobEventLog();
lg.Platform_isMobile();
lg.Controller = SafeValue.SafeString(job["user"]);
lg.Lat = SafeValue.SafeString(job["Lat"]);
lg.Lng = SafeValue.SafeString(job["Lng"]);
lg.setActionLevel(No, CtmJobEventLogRemark.Level.Invoice, 6);
string sql = string.Format(@"delete from job_cost where Id=@No");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@No", No, SqlDbType.Int));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (res.status && res.context == "1")
{
status = true;
lg.log();
}
Common.WriteJsonP(status, Common.StringToJson(""));
}
public void Performance_EditSave(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
JArray jar = (JArray)JsonConvert.DeserializeObject(job["selectedList"].ToString());
string fromName = job["user"].ToString();
string toName = job["Name"].ToString();
string sql = "";
List <ConnectSql_mb.cmdParameters> list = null;
DataTable dt = null;
for (int i = 0; i < jar.Count; i++)
{
int itemId = SafeValue.SafeInt(jar[i]["Id"], 0);
int valueIndex = SafeValue.SafeInt(jar[i]["value"], 0);
if (valueIndex > 0 && valueIndex <= 5)
{
sql = string.Format(@"select type_id,cate_id,Id as item_id,0 as case_id,pts_value1,pts_value2,pts_value3,pts_value4,pts_value5 from pts_item where Id=@Id");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Id", itemId, SqlDbType.Int));
dt = ConnectSql_mb.GetDataTable(sql, list);
if (dt.Rows.Count > 0)
{
int type_id = SafeValue.SafeInt(dt.Rows[0]["type_id"], 0);
int cate_id = SafeValue.SafeInt(dt.Rows[0]["cate_id"], 0);
int item_id = itemId;
int case_id = SafeValue.SafeInt(dt.Rows[0]["case_id"], 0);
decimal pts_value = SafeValue.SafeDecimal(dt.Rows[0]["pts_value" + valueIndex]);
sql = string.Format(@"insert into pts_record (type_id,cate_id,item_id,case_id,pts_index,pts_value,review_time,review_user,pts_user)
values (@type_id,@cate_id,@item_id,@case_id,@pts_index,@pts_value,getdate(),@review_user,@pts_user)
select @@Identity");
list.Add(new ConnectSql_mb.cmdParameters("@type_id", type_id, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@cate_id", cate_id, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@item_id", item_id, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@case_id", case_id, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@pts_index", valueIndex, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@pts_value", pts_value, SqlDbType.Decimal));
list.Add(new ConnectSql_mb.cmdParameters("@review_user", fromName, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@pts_user", toName, SqlDbType.NVarChar, 100));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteScalar(sql, list);
if (res.status)
{
//===========log
C2.CtmJobEventLog lg = new C2.CtmJobEventLog();
lg.Platform_isMobile();
lg.Controller = SafeValue.SafeString(job["user"]);
lg.Lat = SafeValue.SafeString(job["Lat"]);
lg.Lng = SafeValue.SafeString(job["Lng"]);
lg.setActionLevel(SafeValue.SafeInt(res.context, 0), CtmJobEventLogRemark.Level.Performance, 1);
lg.log();
}
}
}
}
Common.WriteJsonP(true, Common.StringToJson(""));
}
protected void btn_upload_Click(object sender, EventArgs e)
{
string _name = "";
try
{
string _desc = "";
UploadedFile[] files = this.file_upload1.UploadedFiles;
_name = (files[0].FileName ?? "").ToLower().Trim();
if (_name.Length == 0)
{
return;
}
string error_text = "";
ProcessFile(_type, _id, _name, _desc, files[0].FileBytes, out error_text);
//InsertPay();
//Auto Payment
this.lb_txt.Text = error_text.Length > 0 ? error_text : "Upload Error";
}
catch (Exception ex)
{
this.lb_txt.Text = "Upload File fail, pls try agin, error:" + ex.Message + ex.StackTrace;
string user = HttpContext.Current.User.Identity.Name;
string sql1 = string.Format(@"insert into CTM_JobEventLog (CreateDatetime,Controller,JobType,Remark) values (getdate(),@Controller,@JobType,@Remark)");
List <ConnectSql_mb.cmdParameters> list1 = new List <ConnectSql_mb.cmdParameters>();
list1.Add(new ConnectSql_mb.cmdParameters("@Controller", user, SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@JobType", "PSA", SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@Remark", this.lb_txt.Text, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult re1 = ConnectSql_mb.ExecuteNonQuery(sql1, list1);
}
}
public void Attachment_Delete(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string status = "0";
string context = Common.StringToJson("");
int attachmentId = SafeValue.SafeInt(job["Id"], 0);
C2.CtmJobEventLog lg = new C2.CtmJobEventLog();
lg.Platform_isMobile();
lg.Controller = SafeValue.SafeString(job["user"]);
lg.Lat = SafeValue.SafeString(job["Lat"]);
lg.Lng = SafeValue.SafeString(job["Lng"]);
//lg.fixActionInfo_ByAttachmentId(attachmentId);
//lg.Remark = "Attachment delete file";
lg.setActionLevel(attachmentId, CtmJobEventLogRemark.Level.Attachment, 2);
string sql = string.Format(@"delete from CTM_Attachment where Id=@attachmentId");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@attachmentId", attachmentId, SqlDbType.Int));
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (result.status)
{
status = "1";
//===========log
lg.log();
}
Common.WriteJsonP(status, context);
}
public void EGL_Reporting_Attachment_Add(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
string status = "0";
string context = Common.StringToJson("");
string sql = string.Format(@"insert into Vehicle_Reporting_Attachment (JobType,RefNo,ContainerNo,TripId,FileType,FileName,FilePath,CreateBy,CreateDateTime,FileNote) values(@JobType,@RefNo,@ContainerNo,@TripId,@FileType,@FileName,@FilePath,@CreateBy,Getdate(),@FileNote)
select @@Identity");
string fileStart = System.Configuration.ConfigurationManager.AppSettings["MobileServerUrl"].ToString();
if (fileStart == null)
{
fileStart = "";
}
string fileEnd = job["FilePath"].ToString();
fileEnd = fileEnd.Substring(0, fileEnd.LastIndexOf('/')) + "/500/" + fileEnd.Substring(fileEnd.LastIndexOf('/') + 1);
fileEnd = fileStart + fileEnd;
cpar = new ConnectSql_mb.cmdParameters("@JobType", job["ContainerNo"], SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@RefNo", job["JobNo"], SqlDbType.NVarChar, 100);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@ContainerNo", job["ContainerNo"], SqlDbType.NVarChar, 100);
//list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@TripId", job["TripId"], SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@FileType", job["FileType"], SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@FileName", job["FileName"], SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@FilePath", fileEnd, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CreateBy", job["CreateBy"], SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@FileNote", job["FileNote"], SqlDbType.NVarChar, 100);
list.Add(cpar);
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteScalar(sql, list);
if (result.status)
{
status = "1";
//===========log
C2.CtmJobEventLog lg = new C2.CtmJobEventLog();
lg.Platform_isMobile();
lg.Controller = SafeValue.SafeString(job["user"]);
lg.Lat = SafeValue.SafeString(job["Lat"]);
lg.Lng = SafeValue.SafeString(job["Lng"]);
lg.setActionLevel(SafeValue.SafeInt(result.context, 0), CtmJobEventLogRemark.Level.Attachment, 1, ":" + job["FileType"] + "[" + job["FileName"] + "]");
lg.log();
}
Common.WriteJsonP(status, context);
}
private string update_cont(int id, string jobNo, string contNo, string sealNo, string contType, DateTime schedule, string contIndex
, string clearStatus, DateTime clearDate, string verify, DateTime verifyDate, string remark, DateTime valiDate, DateTime scheduleStartDate)
{
string result = "";
#region list
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.JobHouse), "JobNo='" + jobNo + "' and ContNo='" + contIndex + "'");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count > 0)
{
#region
for (int j = 0; j < objSet.Count; j++)
{
C2.JobHouse house = objSet[j] as C2.JobHouse;
house.ContNo = contNo;
house.OpsType = "Export";
house.CargoStatus = "C";
C2.Manager.ORManager.StartTracking(house, Wilson.ORMapper.InitialState.Updated);
C2.Manager.ORManager.PersistChanges(house);
}
#endregion
}
string sql = "";
List <ConnectSql_mb.cmdParameters> cmd = new List <ConnectSql_mb.cmdParameters>();
sql = string.Format(@"update ctm_jobdet1 set ContainerNo=@ContainerNo,SealNo=@SealNo,
ContainerType=@ContainerType,ScheduleDate=@ScheduleDate,StatusCode=@StatusCode,CustomsClearDate=@CustomsClearDate,
CustomsClearStatus=@CustomsClearStatus,CustomsVerifyInd=@CustomsVerifyInd,CustomsVerifyDate=@CustomsVerifyDate,CustomsRemark=@CustomsRemark,ValiDate=@ValiDate,ScheduleStartDate=@ScheduleStartDate
where JobNo=@JobNo and ContainerNo=@ContNo");
cmd = new List <ConnectSql_mb.cmdParameters>();
cmd.Add(new ConnectSql_mb.cmdParameters("@ContainerNo", contNo, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@SealNo", sealNo, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@ContainerType", contType, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@JobNo", jobNo, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@ContNo", contIndex, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@StatusCode", "Export", SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@ScheduleDate", schedule, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsClearDate", clearDate, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsClearStatus", clearStatus, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsVerifyInd", verify, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsVerifyDate", verifyDate, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsRemark", remark, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@ValiDate", valiDate, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@ScheduleStartDate", scheduleStartDate, SqlDbType.DateTime));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteNonQuery(sql, cmd);
if (res.status)
{
result = "Save Success!";
}
#endregion
return(result);
}
public void HR_Leave_detail_myaddNew(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string user = SafeValue.SafeString(job["user"]);
string LeaveType = SafeValue.SafeString(job["LeaveType"]);
string Date1 = SafeValue.SafeString(job["Date1"]);
string Time1 = SafeValue.SafeString(job["Time1"]);
string Date2 = SafeValue.SafeString(job["Date2"]);
string Time2 = SafeValue.SafeString(job["Time2"]);
string Remark = SafeValue.SafeString(job["Remark"]);
if (Time1 == "")
{
Time1 = "AM";
}
if (Time2 == "")
{
Time2 = "PM";
}
bool status = false;
string context = "";
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@user", user, SqlDbType.NVarChar, 100));
string sql = string.Format(@"select top 1 Id from Hr_Person where name=@user");
int applyBy = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(sql, list).context, 0);
if (applyBy > 0)
{
sql = string.Format(@"insert into Hr_Leave
(Person,LeaveType,Date1,Time1,Date2,Time2,Remark,Days,ApplyDateTime,ApproveStatus) values(
@PersonBy,@LeaveType,@Date1,@Time1,@Date2,@Time2,@Remark,datediff(day,@Date1,@Date2)+1+(case @Time1 when 'AM' then 0 else -0.5 end)+(case @Time2 when 'PM' then 0 else -0.5 end),getdate(),'Draft')");
list.Add(new ConnectSql_mb.cmdParameters("@PersonBy", applyBy, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@LeaveType", LeaveType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Date1", Date1, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Time1", Time1, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Date2", Date2, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Time2", Time2, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Remark", Remark, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (result.status)
{
status = true;
}
}
else
{
context = "Requair add this user in HR Employee";
}
Common.WriteJsonP(status, Common.StringToJson(context));
}
private void insert_cargo(int id)
{
string sql = string.Format(@"insert into job_stock(OrderId,SortIndex,Marks1,Marks2,Qty2,Price1) values(@OrderId,100,@Marks1,@Marks2,1,380) ");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@OrderId", id, SqlDbType.Int, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Marks1", "OCEAN FREIGHT", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Marks2", "海运费", SqlDbType.NVarChar, 100));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (res.status)
{
}
}
private void create_alert(string RefNo, string JobNo, string JobType, string DocNo, string DocType, string SendType, string SendMethod,
string SendFrom, string alertTo, string alertSubject, string alertBody, DateTime today, string alertCc, string alertBcc)
{
string sql = string.Format(@"insert into sys_alert(RefNo,JobNo,JobType,DocNo,DocType,SendType,SendMethod,SendFrom,SendTo,Subject,Message,StatusCode,CreateUser,CreateTime)
values(@RefNo,@JobNo,@JobType,@DocNo,@DocType,@SendType,@SendMethod,@SendFrom,@SendTo,@Subject,@Message,@StatusCode,@CreateUser,@CreateTime) select @@IDENTITY ");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@RefNo", RefNo, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", JobNo, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", JobType, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@DocNo", DocNo, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@DocType", DocType, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@SendType", SendType, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@SendMethod", SendMethod, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@SendFrom", SendFrom, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@SendTo", alertTo, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@Subject", alertSubject, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@Message", alertBody, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@StatusCode", "Pending", SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@CreateUser", "", SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@CreateTime", today, SqlDbType.DateTime));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteScalar(sql, list);
int id = 0;
if (res.status)
{
id = SafeValue.SafeInt(res.context, 0);
string emailCC = System.Configuration.ConfigurationManager.AppSettings["EmailCc"].ToString();
string emailBcc = System.Configuration.ConfigurationManager.AppSettings["EmailBcc"].ToString();
string err = Helper.Email.SendEmail(alertTo, alertCc, alertBcc, alertSubject, alertBody, "");
if (err.Length > 0)
{
list = new List <ConnectSql_mb.cmdParameters>();
sql = string.Format(@"update sys_alert set StatusCode=@StatusCode where Id=@Id");
list.Add(new ConnectSql_mb.cmdParameters("@StatusCode", err, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@Id", id, SqlDbType.Int));
ConnectSql_mb.ExecuteNonQuery(sql, list);
}
else
{
list = new List <ConnectSql_mb.cmdParameters>();
sql = string.Format(@"update sys_alert set StatusCode=@StatusCode where Id=@Id");
list.Add(new ConnectSql_mb.cmdParameters("@StatusCode", "Success", SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@Id", id, SqlDbType.Int));
ConnectSql_mb.ExecuteNonQuery(sql, list);
}
}
}
public void ContainerTrip_Detail_Delete(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
int status = 0;
string deleteType = job["deleteType"].ToString();
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Id", SafeValue.SafeInt(job["Id"], 0), SqlDbType.Int));
string sql = string.Format(@"select StatusCode from CTM_JobDet2 where Id=@Id");
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["StatusCode"].ToString() == "S")
{
status = 2;
if (deleteType == "S")
{
status = 0;
}
}
if (status <= 0)
{
//===========log
C2.CtmJobEventLog lg = new C2.CtmJobEventLog();
lg.Platform_isMobile();
lg.Controller = SafeValue.SafeString(job["user"]);
lg.Lat = SafeValue.SafeString(job["Lat"]);
lg.Lng = SafeValue.SafeString(job["Lng"]);
//lg.ActionLevel_isTRIP(SafeValue.SafeInt(job["Id"], 0));
//lg.Remark = "Trip Delete";
lg.setActionLevel(SafeValue.SafeInt(job["Id"], 0), CtmJobEventLogRemark.Level.Trip, 2);
lg.log();
sql = string.Format(@"delete from CTM_JobDet2 where Id=@Id");
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (result.status)
{
status = 1;
}
}
}
Common.WriteJsonP(status, Common.StringToJson(job["Id"].ToString()));
}
private void Update_Inline(DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e, int rowIndex)
{
if (rowIndex < 0)
{
e.Result = "Save Error";
return;
}
TextBox txt_tripId = this.grid_Transport.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid_Transport.Columns["tripDetail"], "txt_tripId") as TextBox;
ASPxButtonEdit btn_Driver = this.grid_Transport.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid_Transport.Columns["tripDetail"], "btn_Driver") as ASPxButtonEdit;
ASPxButtonEdit txt_trailer = this.grid_Transport.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid_Transport.Columns["tripDetail"], "txt_trailer") as ASPxButtonEdit;
ASPxButtonEdit txt_parkingLot = this.grid_Transport.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid_Transport.Columns["tripDetail"], "txt_parkingLot") as ASPxButtonEdit;
ASPxMemo txt_toAddress = this.grid_Transport.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid_Transport.Columns["tripDetail"], "txt_toAddress") as ASPxMemo;
string sql = string.Format(@"select DriverCode from ctm_jobdet2 where Id=@Id");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Id", txt_tripId.Text, SqlDbType.Int));
string Driver_old = ConnectSql_mb.ExecuteScalar(sql, list).context;
string re = HttpContext.Current.User.Identity.Name + "," + txt_tripId.Text + "," + btn_Driver.Text;
if (!btn_Driver.Text.Equals(Driver_old))
{
re += "," + Driver_old;
}
sql = string.Format(@"update ctm_jobdet2 set
ToParkingLot=@ToParkingLot,ToCode=@ToCode,DriverCode=@DriverCode,ChessisCode=@ChessisCode
where Id=@Id");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Id", txt_tripId.Text, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@ToParkingLot", txt_parkingLot.Text, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ToCode", txt_toAddress.Text, SqlDbType.NVarChar, 300));
list.Add(new ConnectSql_mb.cmdParameters("@DriverCode", btn_Driver.Text, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ChessisCode", txt_trailer.Text, SqlDbType.NVarChar, 100));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (res.status)
{
e.Result = "success:" + re;
}
else
{
e.Result = "Save Error:" + res.context;
}
//e.Result = txt_tripId.Text;
}
public void Cost_AddNew(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
bool status = false;
string code = job["code"].ToString();
string des = job["name"].ToString();
decimal value = SafeValue.SafeDecimal(job["value"], 0);
int TripNo = SafeValue.SafeInt(job["TripNo"], 0);
string LineType = job["LineType"].ToString();
string sql = string.Format(@"select det2.Id,job.JobNo,job.JobType,det1.ContainerNo,det1.ContainerType
from ctm_jobdet2 as det2
left outer join CTM_JobDet1 as det1 on det2.Det1Id=det1.Id
left outer join CTM_Job as job on det2.JobNo=job.JobNo
where det2.Id=@TripNo");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@TripNo", TripNo, SqlDbType.Int));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
sql = string.Format(@"insert into job_cost (LineId,LineType,JobNo,JobType,ContNo,ContType,TripNo,VendorId,ChgCode,ChgCodeDes,Remark,Qty,Price,CurrencyId,ExRate,DocAmt,LocAmt,CompanyId)
values((select count(*) from job_cost where JobNo=@JobNo),@LineType,@JobNo,@JobType,@ContNo,@ContType,@TripNo,'',@ChgCode,@ChgCodeDes,'',1,@Price,'SGD',1,0,0,0)
select @@Identity");
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", dt.Rows[0]["JobNo"], SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineType", LineType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", dt.Rows[0]["JobType"], SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ContNo", dt.Rows[0]["ContainerNo"], SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ContType", dt.Rows[0]["ContainerType"], SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ChgCode", code, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ChgCodeDes", des, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Price", value, SqlDbType.Decimal));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteScalar(sql, list);
if (res.status)
{
status = true;
//===========log
C2.CtmJobEventLog lg = new C2.CtmJobEventLog();
lg.Platform_isMobile();
lg.Controller = SafeValue.SafeString(job["user"]);
lg.Lat = SafeValue.SafeString(job["Lat"]);
lg.Lng = SafeValue.SafeString(job["Lng"]);
lg.setActionLevel(SafeValue.SafeInt(res.context, 0), CtmJobEventLogRemark.Level.Invoice, 5);
lg.log();
}
Common.WriteJsonP(status, Common.StringToJson(""));
}
private void Job_Save(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string sql = string.Format(@"update ctm_jobdet1 set SealNo=@SealNo,ContainerNo=@ContainerNo,
EmailInd=@EmailInd,F5Ind=@F5Ind,PortnetStatus=@PortnetStatus,UrgentInd=@UrgentInd,WarehouseStatus=@WarehouseStatus
where Id=@Id");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Id", lb_ContId.Text, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@SealNo", txt_sealno.Text, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ContainerNo", txt_ContNo.Text, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@EmailInd", SafeValue.SafeString(cbb_EmailInd.Value), SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@F5Ind", SafeValue.SafeString(cbb_F5Ind.Value), SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@PortnetStatus", SafeValue.SafeString(cbb_PortnetStatus.Value), SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@UrgentInd", SafeValue.SafeString(cbb_UrgentInd.Value), SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@WarehouseStatus", SafeValue.SafeString(cbb_warehouse_status.Value), SqlDbType.NVarChar, 100));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (res.status)
{
sql = string.Format(@"update ctm_job set PickupFrom=@PickupFrom,DeliveryTo=@DeliveryTo,YardRef=@YardRef,SpecialInstruction=@SpecialInstruction,Remark=@Remark,PermitNo=@PermitNo where JobNo=@JobNo");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", lbl_JobNo.Text, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@PickupFrom", txt_from.Text, SqlDbType.NVarChar, 300));
list.Add(new ConnectSql_mb.cmdParameters("@DeliveryTo", txt_to.Text, SqlDbType.NVarChar, 300));
list.Add(new ConnectSql_mb.cmdParameters("@YardRef", txt_depot.Text, SqlDbType.NVarChar, 300));
list.Add(new ConnectSql_mb.cmdParameters("@SpecialInstruction", txt_instruction.Text, SqlDbType.NVarChar, 300));
list.Add(new ConnectSql_mb.cmdParameters("@Remark", txt_Remark.Text, SqlDbType.NVarChar, 300));
list.Add(new ConnectSql_mb.cmdParameters("@PermitNo", txt_PermitNo.Text, SqlDbType.NVarChar, 100));
res = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (res.status)
{
sql = string.Format(@"update ctm_jobdet2 set ContainerNo=@ContainerNo where Det1Id=@Det1Id");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Det1Id", lb_ContId.Text, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@ContainerNo", txt_ContNo.Text, SqlDbType.NVarChar, 100));
res = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (res.status)
{
update_cargo(lb_ContId.Text, txt_ContNo.Text);
e.Result = "Success";
}
}
}
}
public void Attendance_EditSave(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string user = job["user"].ToString();
string action = job["action"].ToString();
string sql = string.Format(@"insert into pts_attendance (createdate,record_type,record_user,record_note)
values(getdate(),@record_type,@record_user,@record_note)
select @@Identity");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@record_type", action, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@record_user", user, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@record_note", "", SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteScalar(sql, list);
if (result.status && action.ToLower().IndexOf("in") >= 0)
{
sql = string.Format(@"insert into pts_record (type_id,cate_id,item_id,case_id,pts_index,pts_value,review_time,review_user,pts_user)
values (@type_id,@cate_id,@item_id,@case_id,@pts_index,@pts_value,getdate(),@review_user,@pts_user)");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@type_id", 0, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@cate_id", 0, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@item_id", 0, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@case_id", 0, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@pts_index", 0, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@pts_value", 10, SqlDbType.Decimal));
list.Add(new ConnectSql_mb.cmdParameters("@review_user", user, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@pts_user", user, SqlDbType.NVarChar, 100));
ConnectSql_mb.ExecuteNonQuery(sql, list);
//===========log
C2.CtmJobEventLog lg = new C2.CtmJobEventLog();
lg.Platform_isMobile();
lg.Controller = SafeValue.SafeString(job["user"]);
lg.Lat = SafeValue.SafeString(job["Lat"]);
lg.Lng = SafeValue.SafeString(job["Lng"]);
lg.setActionLevel(SafeValue.SafeInt(result.context, 0), CtmJobEventLogRemark.Level.Attendance, 1);
lg.log();
}
Common.WriteJsonP(true, Common.StringToJson(""));
}
public BizResult delete(string user)
{
BizResult res = new BizResult();
if (det1 == null || det1.Id == 0)
{
res.context = "Data is empty";
}
else
{
if (updating_verify(res, user, det1))
{
string sql = string.Format(@"delete from ctm_jobdet1 where Id=@Id");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Id", det1.Id, SqlDbType.Int));
ConnectSql_mb.sqlResult sqlRes = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (sqlRes.status)
{
res.status = true;
CtmJobEventLog lg = new CtmJobEventLog();
lg.Platform_isBackend();
lg.Controller = user;
//lg.JobNo = det1.JobNo;
//lg.ContainerNo = det1.ContainerNo;
//lg.Remark = "ContDelete";
lg.setActionLevel(det1.Id, CtmJobEventLogRemark.Level.Container, 2);
lg.log();
System.Collections.ObjectModel.Collection <C2.CtmJobDet2> det2 = C2.Manager.ORManager.GetCollection <C2.CtmJobDet2>("det1Id=" + det1.Id);
C2.CtmJobDet2Biz det2Bz = new CtmJobDet2Biz(0);
foreach (C2.CtmJobDet2 temp in det2)
{
det2Bz.setData(temp);
det2Bz.delete(user);
}
//sql = string.Format(@"delete from ctm_jobdet2 where det1Id=@Id");
//ConnectSql_mb.ExecuteNonQuery(sql, list);
}
}
}
return(res);
}
public void Review_Detail_Save(string info)
{
string info_ = HttpUtility.UrlDecode(Server.UrlDecode(info));
JObject job = (JObject)JsonConvert.DeserializeObject(info_);
bool status = false;
string context = Common.StringToJson("");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Oid", SafeValue.SafeInt(job["Oid"], 0), SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@Qty", SafeValue.SafeInt(job["Qty"], 0), SqlDbType.Decimal));
list.Add(new ConnectSql_mb.cmdParameters("@Weight", SafeValue.SafeDecimal(job["Weight"]), SqlDbType.Decimal));
list.Add(new ConnectSql_mb.cmdParameters("@M3", SafeValue.SafeDecimal(job["M3"]), SqlDbType.Decimal));
list.Add(new ConnectSql_mb.cmdParameters("@PackType", job["PackType"], SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Markings", job["Markings"], SqlDbType.NVarChar, 300));
list.Add(new ConnectSql_mb.cmdParameters("@Contents", job["Contents"], SqlDbType.NVarChar, 300));
//string sql = string.Format(@"update job_receipt set Qty=@Qty,Weight=@Weight,M3=@M3,PackType=@PackType,Markings=@Markings,Contents=@Contents where row_id=@Oid");
string sql = string.Format(@"update job_house set QtyOrig=@Qty,WeightOrig=@Weight,VolumeOrig=@M3,PackTypeOrig=@PackType,Marking1=@Markings,Remark1=@Contents where Id=@Oid");
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (result.status)
{
status = true;
context = Common.StringToJson(result.context);
//===========log
C2.CtmJobEventLog lg = new C2.CtmJobEventLog();
lg.Platform_isMobile();
lg.Controller = SafeValue.SafeString(job["user"]);
lg.Lat = SafeValue.SafeString(job["Lat"]);
lg.Lng = SafeValue.SafeString(job["Lng"]);
//lg.ActionLevel_isCARGO(SafeValue.SafeInt(job["Oid"], 0));
//lg.Remark = "Cargo update";
lg.setActionLevel(SafeValue.SafeInt(job["Oid"], 0), CtmJobEventLogRemark.Level.Cargo, 3);
lg.log();
}
Common.WriteJsonP(status, context);
}
public void HR_Leave_detail_save(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
int jobId = SafeValue.SafeInt(job["Id"], 0);
int applyBy = SafeValue.SafeInt(job["Person"], 0);
string LeaveType = SafeValue.SafeString(job["LeaveType"]);
string Date1 = SafeValue.SafeString(job["Date1"]);
string Time1 = SafeValue.SafeString(job["Time1"]);
string Date2 = SafeValue.SafeString(job["Date2"]);
string Time2 = SafeValue.SafeString(job["Time2"]);
string Remark = SafeValue.SafeString(job["Remark"]);
if (Time1 == "")
{
Time1 = "AM";
}
if (Time2 == "")
{
Time2 = "PM";
}
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
string sql = string.Format(@"update Hr_Leave
set Person=@PersonBy,LeaveType=@LeaveType,Date1=@Date1,Time1=@Time1,Date2=@Date2,Time2=@Time2,Remark=@Remark,
Days=datediff(day,@Date1,@Date2)+1+(case @Time1 when 'AM' then 0 else -0.5 end)+(case @Time2 when 'PM' then 0 else -0.5 end)
where Id=@Id");
list.Add(new ConnectSql_mb.cmdParameters("@Id", jobId, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@PersonBy", applyBy, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@LeaveType", LeaveType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Date1", Date1, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Time1", Time1, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Date2", Date2, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Time2", Time2, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Remark", Remark, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteNonQuery(sql, list);
Common.WriteJsonP(result.status, Common.StringToJson(result.context));
}
private bool is_created(string id, DateTime billDate)
{
bool action = true;
string sql = string.Format(@"select count(*) from job_cost where RelaId=@RelaId and CONVERT(varchar(100), BillStartDate, 23)=@BillStartDate");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@RelaId", id, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@BillStartDate", billDate.Date.ToString("yyyy-MM-dd"), SqlDbType.DateTime));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteScalar(sql, list);
int n = 0;
if (res.status)
{
n = SafeValue.SafeInt(res.context, 0);
}
if (n >= 1)
{
action = false;
}
return(action);
}
public void HR_Leave_view_changeStatus(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
int jobId = SafeValue.SafeInt(job["Id"], 0);
string ApproveStatus = SafeValue.SafeString(job["status"]);
string ApproveBy = SafeValue.SafeString(job["user"]);
string ApproveRemark = SafeValue.SafeString(job["remark"]);
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
string sql = string.Format(@"update Hr_Leave
set ApproveStatus=@ApproveStatus,ApproveDate=getdate(),ApproveTime=replace(convert(nvarchar(5),getdate(),114),':',''),
ApproveBy=isnull((select top 1 Id from Hr_person where Name=@ApproveBy),0),ApproveRemark=@ApproveRemark
where Id=@Id");
list.Add(new ConnectSql_mb.cmdParameters("@Id", jobId, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@ApproveStatus", ApproveStatus, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ApproveBy", ApproveBy, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ApproveRemark", ApproveRemark, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteNonQuery(sql, list);
Common.WriteJsonP(result.status, Common.StringToJson(result.context));
}
public void User_register_Submit(string info)
{
JObject jo = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string mobile = SafeValue.SafeString(jo["account"]);
string pw = SafeValue.SafeString(jo["password"]);
string username = SafeValue.SafeString(jo["userName"]);
bool status = false;
string context = "";
Encryption.EncryptClass encrypt = new Encryption.EncryptClass();
string pw_mm = SafeValue.SafeString(encrypt.DESEnCode(username, pw), "");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Name", username, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Pwd", pw_mm, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Tel", mobile, SqlDbType.NVarChar, 100));
string sql = string.Format(@"select * from [User] where Name=@Name,Tel=@Tel");
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
if (dt.Rows.Count == 0)
{
sql = string.Format(@"insert into [User] (Name,Pwd,Email,Tel,Role,IsActive,CustId,Port) values(@Name,@Pwd,'',@Tel,'Client',1,'','')");
ConnectSql_mb.sqlResult result = ConnectSql_mb.ExecuteNonQuery(sql, list);
status = result.status;
if (!status)
{
context = result.context;
}
}
else
{
context = "This user name or mobile have be registered";
}
context = Common.StringToJson(context);
Common.WriteJsonP(status, context);
}
public void ImportJob(string batch, string file, out string error_text)
{
//throw new Exception("importjob");
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
//Workbook wb = new Workbook();
byte[] byData = new byte[100];
char[] charData = new char[1000];
List <string> list = new List <string>();
if (file.ToLower().EndsWith(".txt"))
{
using (System.IO.StreamReader sr = new System.IO.StreamReader(file, true))
{
string str;
while ((str = sr.ReadLine()) != null)
{
list.Add(str);
}
}
}
int empty_i = 0;
string re_text = "";
//Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
string notInside = "";
int insideDo = 0;
string sql = "";
//throw new Exception("here");
//=================================== version 1
bool beginImport = false;
int _row = 0;
DateTime payDate = DateTime.Today;
string value = "";
string cur = "SGD";
int docId = 0;
int index = 0;
for (int i = 0; i < list.Count; i++)
{
_row++;
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string line = list[i];
string[] ar = line.Split('~');
if (i == 0)
{
ar = line.Split(' ');
payDate = SafeValue.SafeDate(ar[7], date);
docId = InsertPay(payDate, cur);
}
if (i == list.Count - 1)
{
ar = line.Split('~');
decimal locAmt = SafeValue.SafeDecimal(ar[4]);
sql = string.Format(@"update XAApPayment set DocAmt={1}, LocAmt={1} where SequenceId={0}", docId, locAmt);
ConnectSql_mb.ExecuteNonQuery(sql);
continue;
}
if (i == 4)
{
ar = line.Split(':');
cur = ar[1];
}
if (ar[0] == "Invoice No.")
{
beginImport = true;
empty_i++;
continue;
}
//throw new Exception(ws.Cells["A" + i].Type.ToString());
try
{
value = list[i];
}
catch
{
empty_i++;
continue;
}
if (line.Length <= 0)
{
empty_i++;
continue;
}
empty_i = 0;
if (beginImport)
{
index++;
string invNo = SafeValue.SafeString(ar[0]);
DateTime invDate = SafeValue.SafeDate(ar[1], date);
//string docType = SafeValue.SafeString(ar[2]);
decimal amt = SafeValue.SafeDecimal(ar[4]);
//=====================检测是否存在Bill Number
string billDate = invDate.ToString("yyyy-MM-dd");
string sql_check = string.Format(@"select count(*) from XAApPaymentDet where Remark1='{0}'", invNo);
int check_n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
if (check_n > 0)
{
existDo++;
continue;
}
else
{
try
{
sql_check = string.Format(@"select SequenceId,DocNo,DocType,CurrencyId,DocDate from XAApPayable where SupplierBillNo='{0}' and SupplierBillDate='{1}'", invNo, billDate);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
string docNo = SafeValue.SafeString(dt_check.Rows[0]["DocNo"]);
string docType = SafeValue.SafeString(dt_check.Rows[0]["DocType"]);
DateTime docDate = SafeValue.SafeDate(dt_check.Rows[0]["DocDate"], DateTime.Today);
InsertPay_Det(docId, docNo, docType, index, invNo, "", "", 1, amt, cur, 1, 0, "", "", "", "", invDate);
successJob++;
}
else
{
insideDo++;
if (insideDo == 1)
{
notInside += invNo;
}
else
{
notInside += invNo + ",";
}
}
}
catch (Exception ex)
{
//errorDo++;
throw new Exception(_row.ToString() + "##" + ex.Message);
}
}
}
else
{
}
}
sql = string.Format(@"select DocNo,DocDate,LocAmt from XAApPayment where SequenceId={0}", docId);
DataTable dt_pay = ConnectSql_mb.GetDataTable(sql);
string billNo = "";
decimal totalAmt = 0;
DateTime docDate1 = DateTime.Today;
if (dt_pay.Rows.Count > 0)
{
billNo = SafeValue_mb.SafeString(dt_pay.Rows[0]["DocNo"]);
totalAmt = SafeValue.SafeDecimal(dt_pay.Rows[0]["LocAmt"]);
docDate1 = SafeValue.SafeDate(dt_pay.Rows[0]["DocDate"], DateTime.Today);
}
re_text = string.Format(@"uploaded {0} lines", successJob);
re_text += successItem > 0 ? string.Format(@" {0} items.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} existed", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} error", errorDo) : "";
re_text += insideDo > 0 ? string.Format(@" ,{0} not inside the payable", "Invoice:【" + notInside + "】") : "";
error_text = re_text;
string user = HttpContext.Current.User.Identity.Name;
string sql1 = string.Format(@"insert into CTM_JobEventLog (CreateDatetime,Controller,JobType,Remark,Note1,Note2,Value1) values (getdate(),@Controller,@JobType,@Remark,@Note1,@Note2,@Value1)");
List <ConnectSql_mb.cmdParameters> list1 = new List <ConnectSql_mb.cmdParameters>();
list1.Add(new ConnectSql_mb.cmdParameters("@Controller", user, SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@JobType", "GIRO", SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@Remark", re_text, SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Note1", billNo, SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Note2", docDate1.ToString("yyyy-MM-dd"), SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Value1", totalAmt, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult re1 = ConnectSql_mb.ExecuteNonQuery(sql1, list1);
}
protected bool UpdateParty(string name, string uen, string ic, string email1, string email2, string tel1, string tel2, string mobile1, string mobile2, string address, string responsible)
{
string sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and (CrNo='{1}' or CrNo='{2}')", name, uen, ic);
int count = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
bool action = false;
if (count > 0)
{
#region Update Party
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
string code = "";
if (name.Length > 4)
{
if (responsible != "个人")
{
code = name.Substring(0, 4) + DateTime.Today.Second;
}
else
{
code = name;
}
}
else
{
code = name;
}
if (ic.Length > 0)
{
#region
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", name, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CrNo", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Value", "like '%" + name + "%'", SqlDbType.NVarChar, 100);
list.Add(cpar);
sql = string.Format(@"update XXParty set Code=@Code,Name=@Name,Address=@Address,Tel1=@Tel1,Tel2=@Tel2,Fax1=@Fax1,Fax2=@Fax2,Email1=@Email1,Email2=@Email2,CrNo=@CrNo where Name=@Value or CrNo=@CrNo");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
#endregion
}
if (uen.Length > 0)
{
#region
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", name, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CrNo", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Value", "like '%" + name + "%'", SqlDbType.NVarChar, 100);
list.Add(cpar);
sql = string.Format(@"update XXParty set Code=@Code,Name=@Name,Address=@Address,Tel1=@Tel1,Tel2=@Tel2,Fax1=@Fax1,Fax2=@Fax2,Email1=@Email1,Email2=@Email2,CrNo=@CrNo where Name=@Value or CrNo=@CrNo");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
#endregion
}
#endregion
}
return(action);
}
protected bool VilaParty(string code, string uen, string ic, string email1, string email2, string tel1, string tel2, string mobile1, string mobile2, string address, string responsible)
{
bool action = false;
string sql = string.Format(@"select top 1 SequenceId from XXParty order by SequenceId desc");
int sequenceId = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
if (responsible == "个人")
{
sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and CrNo='{1}' and Tel1='{2}'", code, ic, tel1);
}
else
{
sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and CrNo='{1}' and Tel1='{2}'", code, uen, tel1);
}
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
if (n > 0)
{
action = true;
}
else
{
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
sequenceId = sequenceId + 1;
#region list add
if (code.Length > 4)
{
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code + "_" + sequenceId, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code.Substring(0, 6) + "_" + sequenceId, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code + DateTime.Today.Second, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Status", "USE", SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", mobile1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", mobile2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (ic.Length > 0)
{
cpar = new ConnectSql_mb.cmdParameters("@CrNo", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
if (uen.Length > 0)
{
cpar = new ConnectSql_mb.cmdParameters("@CrNo", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
sql = string.Format(@"insert into XXParty(PartyId,Code,Name,IsCustomer,Status,Address,Tel1,Tel2,Fax1,Fax2,Email1,Email2,CrNo) values(@PartyId,@Code,@Name,1,@Status,@Address,@Tel1,@Tel2,@Fax1,@Fax2,@Email1,@Email2,@CrNo)");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
else
{
}
#endregion
}
return(action);
}
public void ImportExcel(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
int SortIndex = 0;
bool action = false;
//Job Info
string client = ws.Cells["B" + 2].StringValue;
string vessel = ws.Cells["B" + 3].StringValue;
string voyage = ws.Cells["D" + 3].StringValue;
string pol = ws.Cells["B" + 4].StringValue;
string pod = ws.Cells["D" + 4].StringValue;
string warehouse = ws.Cells["H" + 2].StringValue;
string isWarehouse = ws.Cells["J" + 2].StringValue;
DateTime now = DateTime.Now;
string jobNo = "";
C2.CtmJob job = new C2.CtmJob();
jobNo = C2Setup.GetNextNo("", "CTM_Job_WGR", DateTime.Now);
job.JobNo = jobNo;
job.JobType = "WGR";
job.JobDate = DateTime.Now;
job.ClientId = EzshipHelper.GetPartyId(client);
job.DeliveryTo = "";
job.StatusCode = "USE";
job.QuoteNo = jobNo;
job.QuoteStatus = "None";
job.JobStatus = "Confirmed";
job.Vessel = vessel;
job.Voyage = voyage;
job.Pol = pol;
job.Pod = pod;
job.WareHouseCode = warehouse;
job.IsWarehouse = isWarehouse;
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_WGR", jobNo, now);
auto_add_trailer_trip(jobNo, "WGR");
//Order Info
string bkgRefNo = ws.Cells["B" + 7].StringValue;
string hblNo = ws.Cells["F" + 7].StringValue;
string conNo = ws.Cells["I" + 7].StringValue;
string shipper = ws.Cells["B" + 8].StringValue;
string consignee = ws.Cells["B" + 9].StringValue;
string ic_uen = ws.Cells["H" + 9].StringValue;
string responsible = ws.Cells["K" + 9].StringValue;
string email1 = ws.Cells["C" + 10].StringValue;
string email2 = ws.Cells["H" + 10].StringValue;
string tel = ws.Cells["C" + 11].StringValue;
string postCode = ws.Cells["E" + 11].StringValue;
string mobile = ws.Cells["H" + 11].StringValue;
string address = ws.Cells["B" + 12].StringValue;
string receiver = ws.Cells["B" + 14].StringValue;
string contact = ws.Cells["B" + 15].StringValue;
string delivery = ws.Cells["B" + 16].StringValue;
string prepaidInd = ws.Cells["C" + 18].StringValue;
decimal collectAmount1 = SafeValue.SafeDecimal(ws.Cells["H" + 18].StringValue);
decimal collectAmount2 = SafeValue.SafeDecimal(ws.Cells["K" + 18].StringValue);
string dutyPayment = ws.Cells["B" + 19].StringValue;
string incoterm = ws.Cells["B" + 20].StringValue;
decimal ocean_freight = SafeValue.SafeDecimal(ws.Cells["H" + 20].StringValue);
string remark = ws.Cells["B" + 21].StringValue;
string fee1CurrId = ws.Cells["H" + 24].StringValue;
string totalQty = ws.Cells["B" + 24].StringValue;
string packType = ws.Cells["D" + 2].StringValue;
string weight = ws.Cells["F" + 24].StringValue;
string m3 = ws.Cells["J" + 24].StringValue;
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
#region list add
cpar = new ConnectSql_mb.cmdParameters("@BookingNo", bkgRefNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@HblNo", hblNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobNo", jobNo, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@RefNo", jobNo, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ContNo", conNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ShipperInfo", EzshipHelper.GetPartyId(shipper), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeInfo", consignee, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "PERSON", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "COMPANY", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeRemark", ic_uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (email2.Length > 0)
{
email1 += "," + email2;
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeEmail", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeTel", tel, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeZip", postCode, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile1", mobile, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeAddress", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientId", receiver, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientEmail", contact, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientAddress", delivery, SqlDbType.NVarChar, 100);
list.Add(cpar);
string prepaid = "NO";
if (prepaidInd.Equals("√"))
{
prepaid = "YES";
}
cpar = new ConnectSql_mb.cmdParameters("@PrepaidInd", prepaid, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount1", collectAmount1, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount2", collectAmount2, SqlDbType.Decimal, 100);
list.Add(cpar);
string payment = "";
if (dutyPayment.Equals("税中国付/DUTY PAID"))
{
payment = "DUTY PAID";
}
else
{
payment = "DUTY UNPAID";
}
cpar = new ConnectSql_mb.cmdParameters("@DutyPayment", payment, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Incoterm", incoterm, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Ocean_Freight", ocean_freight, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark1", remark, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Currency", fee1CurrId, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Qty", SafeValue.SafeDecimal(totalQty, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@PackTypeOrig", packType, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Weight", SafeValue.SafeDecimal(weight, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Volume", SafeValue.SafeDecimal(m3, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobType", "WGR", SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into job_house (BookingNo,HblNo,JobNo,RefNo,JobType,ContNo,CargoStatus,ShipperInfo,ConsigneeInfo,Responsible,ConsigneeRemark,
ConsigneeEmail,ConsigneeTel,Mobile1,ConsigneeAddress,ClientId,ClientEmail,ClientAddress,Remark1,Prepaid_Ind,Collect_Amount1,Collect_Amount2,Duty_Payment,Incoterm,Ocean_Freight,Currency,Qty,PackTypeOrig,Weight,Volume,CargoType)
values (@BookingNo,@HblNo,@JobNo,@RefNo,@JobType,@ContNo,'P',@ShipperInfo,@ConsigneeInfo,@Responsible,@ConsigneeRemark,@ConsigneeEmail,@ConsigneeTel,@Mobile1,@ConsigneeAddress,@ClientId,@ClientEmail,@ClientAddress,@Remark1,@PrepaidInd,@CollectAmount1,
@CollectAmount2,@DutyPayment,@Incoterm,@Ocean_Freight,@Currency,@Qty,@PackTypeOrig,@Weight,@Volume,'IN') select @@identity");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteScalar(sql, list);
if (re.status)
{
string id = SafeValue.SafeString(re.context);
sql = string.Format(@"update job_house set LineId={0} where Id={0}", id);
ConnectSql_mb.ExecuteNonQuery(sql);
//insert_cargo(SafeValue.SafeInt(id, 0));
if (id.Length > 0)
{
action = true;
for (int i = 25; true; i++)
{
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string A = ws.Cells["A" + i].StringValue;
string B = ws.Cells["B" + i].StringValue;
string C = ws.Cells["C" + i].StringValue;
if (A.Trim().ToUpper().Equals("序号"))
{
beginImport = true;
empty_i++;
i = i + 1;
continue;
}
try
{
SortIndex = SafeValue.SafeInt(ws.Cells["A" + i].StringValue, 0);
}
catch
{
empty_i++;
continue;
}
if (C.Length <= 0 && B.Length <= 0)
{
empty_i++;
continue;
}
//empty_i = 0;
if (beginImport)
{
#region
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
#endregion
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select Marks1 from job_stock where OrderId={0} and SortIndex={1} and Marks1='{2}'", id, SortIndex, C);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
else
{
sql_check = string.Format(@"select count(*) from job_stock where OrderId={0}", id);
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
SortIndex = n + 1;
}
try
{
list = new List <ConnectSql_mb.cmdParameters>();
#region list add
cpar = new ConnectSql_mb.cmdParameters("@SortIndex", SortIndex, SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@B", B, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeInt(E, 0), SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeDecimal(F, 0), SqlDbType.Decimal);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDecimal(G), SqlDbType.Decimal);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@OrderId", id, SqlDbType.Int, 100);
list.Add(cpar);
#endregion
sql = string.Format(@"insert into job_stock (SortIndex,Marks1,Marks2,Uom1,Qty2,Price1,Price2,
OrderId) values (@SortIndex,@B,@C,@D,@E,@F,@G,@OrderId)");
re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
successJob++;
//action = true;
}
else
{
errorDo++;
//throw new Exception(re.context);
}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
}
}
if (action)
{
//VilaParty(consignee, uen, ic, email1, email2, tel1, tel2, mobile1, mobile2, address, responsible);
}
}
}
re_text = string.Format(@"添加了 {0} 条货物", successJob);
re_text += successItem > 0 ? string.Format(@" {0} 条记录.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} 条已存在", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} 条错误", errorDo) : "";
error_text = re_text;
}
public void ImportJob(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
int SortIndex = 0;
bool action = false;
string bkgRefNo = ws.Cells["C" + 2].StringValue;
string jobOrder = ws.Cells["F" + 3].StringValue;
string conNo = ws.Cells["J" + 2].StringValue;
string shipper = ws.Cells["C" + 3].StringValue;
string consignee = ws.Cells["C" + 4].StringValue;
string responsible = ws.Cells["L" + 4].StringValue;
string ic = ws.Cells["D" + 5].StringValue;
string uen = ws.Cells["I" + 5].StringValue;
string email1 = ws.Cells["D" + 6].StringValue;
string email2 = ws.Cells["H" + 6].StringValue;
string tel1 = ws.Cells["D" + 7].StringValue;
string tel2 = ws.Cells["F" + 7].StringValue;
string mobile1 = ws.Cells["H" + 7].StringValue;
string mobile2 = ws.Cells["L" + 7].StringValue;
string address = ws.Cells["C" + 8].StringValue;
string receiver = ws.Cells["C" + 10].StringValue;
string contact = ws.Cells["C" + 11].StringValue;
string delivery = ws.Cells["C" + 12].StringValue;
string payable = ws.Cells["C" + 13].StringValue;
string prepaidInd = ws.Cells["D" + 13].StringValue;
decimal collectAmount1 = SafeValue.SafeDecimal(ws.Cells["H" + 13].StringValue);
decimal collectAmount2 = SafeValue.SafeDecimal(ws.Cells["M" + 13].StringValue);
string dutyPayment = ws.Cells["C" + 14].StringValue;
string incoterm = ws.Cells["C" + 15].StringValue;
decimal miscFee = SafeValue.SafeDecimal(ws.Cells["E" + 15].StringValue);
string remark = ws.Cells["C" + 16].StringValue;
string fee1CurrId = ws.Cells["M" + 19].StringValue;
string totalQty = ws.Cells["I" + 19].StringValue;
string packType = ws.Cells["J" + 19].StringValue;
string weight = ws.Cells["K" + 19].StringValue;
string m3 = ws.Cells["L" + 19].StringValue;
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
#region list add
cpar = new ConnectSql_mb.cmdParameters("@BookingNo", bkgRefNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobNo", 0, SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ContNo", conNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ShipperInfo", shipper, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeInfo", consignee, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "PERSON", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "COMPANY", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeRemark", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeEmail", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile1", mobile1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile2", mobile2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Desc1", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientId", receiver, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientEmail", contact, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark1", delivery, SqlDbType.NVarChar, 100);
list.Add(cpar);
string prepaid = "NO";
if (prepaidInd.Equals("√"))
{
prepaid = "YES";
}
cpar = new ConnectSql_mb.cmdParameters("@PrepaidInd", prepaid, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount1", collectAmount1, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount2", collectAmount2, SqlDbType.Decimal, 100);
list.Add(cpar);
string payment = "";
if (dutyPayment.Equals("税中国付/DUTY PAID"))
{
payment = "DUTY PAID";
}
else
{
payment = "DUTY UNPAID";
}
cpar = new ConnectSql_mb.cmdParameters("@DutyPayment", payment, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Incoterm", incoterm, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Ocean_Freight", miscFee, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark2", remark, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Currency", fee1CurrId, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Qty", SafeValue.SafeInt(totalQty, 0), SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@PackTypeOrig", packType, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Weight", SafeValue.SafeDecimal(weight, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Volume", SafeValue.SafeDecimal(m3, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobType", "E", SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into job_house (BookingNo,JobNo,JobType,ContNo,CargoStatus,ShipperInfo,ConsigneeInfo,Responsible,ConsigneeRemark,
ConsigneeEmail,Email1,Email2,Tel1,Tel2,Mobile1,Mobile2,Desc1,ClientId,ClientEmail,Remark1,Prepaid_Ind,Collect_Amount1,Collect_Amount2,Duty_Payment,Incoterm,Ocean_Freight,Remark2,Currency,Qty,PackTypeOrig,Weight,Volume)
values (@BookingNo,@JobNo,@JobType,@ContNo,'ORDER',@ShipperInfo,@ConsigneeInfo,@Responsible,@ConsigneeRemark,@ConsigneeEmail,@Email1,@Email2,@Tel1,@Tel2,@Mobile1,@Mobile2,@Desc1,@ClientId,@ClientEmail,@Remark1,@PrepaidInd,@CollectAmount1,@CollectAmount2,@DutyPayment,@Incoterm,@Ocean_Freight,@Remark2,@Currency,@Qty,@PackTypeOrig,@Weight,@Volume)");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteScalar(sql, list);
string sql_id = string.Format("select top 1 Id from job_house order by Id desc");
string Oid = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql_id));
insert_cargo(SafeValue.SafeInt(Oid, 0));
if (Oid.Length > 0)
{
action = true;
for (int i = 17; true; i++)
{
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string A = ws.Cells["A" + i].StringValue;
string C = ws.Cells["C" + i].StringValue;
if (A.Trim().ToUpper().Equals("序号"))
{
beginImport = true;
empty_i++;
i = i + 1;
continue;
}
try
{
SortIndex = SafeValue.SafeInt(ws.Cells["A" + i].StringValue, 0);
}
catch
{
empty_i++;
continue;
}
if (C.Length <= 0)
{
empty_i++;
continue;
}
//empty_i = 0;
if (beginImport)
{
#region
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
string H = ws.Cells["H" + i].StringValue;
string I = ws.Cells["I" + i].StringValue;
#endregion
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select Marks1 from job_stock where OrderId={0} and SortIndex={1} and Marks1='{2}'", Oid, SortIndex, C);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
else
{
sql_check = string.Format(@"select count(*) from job_stock where OrderId={0}", Oid);
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
SortIndex = n + 1;
}
try
{
list = new List <ConnectSql_mb.cmdParameters>();
#region list add
cpar = new ConnectSql_mb.cmdParameters("@SortIndex", SortIndex, SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeInt(F, 0), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeInt(F, 0), SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDecimal(G), SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@H", SafeValue.SafeDecimal(H), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@OrderId", Oid, SqlDbType.Int, 100);
list.Add(cpar);
#endregion
sql = string.Format(@"insert into job_stock (SortIndex,Marks1,Marks2,Uom1,Qty2,Price1,Price2,
OrderId) values (@SortIndex,@C,@D,@E,@F,@G,@H,@OrderId)");
re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
successJob++;
//action = true;
}
else
{
errorDo++;
//throw new Exception(re.context);
}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
}
}
if (action)
{
VilaParty(consignee, uen, ic, email1, email2, tel1, tel2, mobile1, mobile2, address, responsible);
}
}
re_text = string.Format(@"添加了 {0} 条货物", successJob);
re_text += successItem > 0 ? string.Format(@" {0} 条记录.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} 条已存在", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} 条错误", errorDo) : "";
error_text = re_text;
}
public void ImportJob(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
for (int i = 1; true; i++)
{
if (empty_i >= 10)
{
break;
}
string A = ws.Cells["A" + i].StringValue;
string B = ws.Cells["B" + i].StringValue;
if (B.Length <= 0)
{
empty_i++;
continue;
}
empty_i = 0;
if (beginImport)
{
string C = ws.Cells["C" + i].StringValue;
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
string H = ws.Cells["H" + i].StringValue;
string I = ws.Cells["I" + i].StringValue;
I = I.Replace(" ", "");
string J = ws.Cells["J" + i].StringValue;
string K = ws.Cells["K" + i].StringValue;
string L = ws.Cells["L" + i].StringValue;
string M = ws.Cells["M" + i].StringValue;
string N = ws.Cells["N" + i].StringValue;
string O = ws.Cells["O" + i].StringValue;
string P = ws.Cells["P" + i].StringValue;
string Q = ws.Cells["Q" + i].StringValue;
string R = ws.Cells["R" + i].StringValue;
string S = ws.Cells["S" + i].StringValue;
string T = ws.Cells["T" + i].StringValue;
string U = ws.Cells["U" + i].StringValue;
string V = ws.Cells["V" + i].StringValue;
string W = ws.Cells["W" + i].StringValue;
string X = ws.Cells["X" + i].StringValue;
string Y = ws.Cells["Y" + i].StringValue;
string Z = ws.Cells["Z" + i].StringValue;
string AA = ws.Cells["AA" + i].StringValue;
string AB = ws.Cells["AB" + i].StringValue;
string AC = ws.Cells["AC" + i].StringValue;
string AD = ws.Cells["AD" + i].StringValue;
string AE = ws.Cells["AE" + i].StringValue;
string AF = ws.Cells["AF" + i].StringValue;
string AG = ws.Cells["AG" + i].StringValue;
string AH = ws.Cells["AH" + i].StringValue;
string AI = ws.Cells["AI" + i].StringValue;
string AJ = ws.Cells["AJ" + i].StringValue;
string AK = ws.Cells["AK" + i].StringValue;
string AL = ws.Cells["AL" + i].StringValue;
string AM = ws.Cells["AM" + i].StringValue;
string AN = ws.Cells["AN" + i].StringValue;
string AO = ws.Cells["AO" + i].StringValue;
string AP = ws.Cells["AP" + i].StringValue;
string AQ = ws.Cells["AQ" + i].StringValue;
string AR = ws.Cells["AR" + i].StringValue;
string AS = ws.Cells["AS" + i].StringValue;
string AT = ws.Cells["AT" + i].StringValue;
string AU = ws.Cells["AU" + i].StringValue;
string AV = ws.Cells["AV" + i].StringValue;
string AW = ws.Cells["AW" + i].StringValue;
string AX = ws.Cells["AX" + i].StringValue;
string AY = ws.Cells["AY" + i].StringValue;
string AZ = ws.Cells["AZ" + i].StringValue;
string BA = ws.Cells["BA" + i].StringValue;
string BB = ws.Cells["BB" + i].StringValue;
string BC = ws.Cells["BC" + i].StringValue;
string BD = ws.Cells["BD" + i].StringValue;
string BE = ws.Cells["BE" + i].StringValue;
string BF = ws.Cells["BF" + i].StringValue;
string BG = ws.Cells["BG" + i].StringValue;
string BH = ws.Cells["BH" + i].StringValue;
string BI = ws.Cells["BI" + i].StringValue;
string BJ = ws.Cells["BJ" + i].StringValue;
string BK = ws.Cells["BK" + i].StringValue;
string BL = ws.Cells["BL" + i].StringValue;
string BM = ws.Cells["BM" + i].StringValue;
string BN = ws.Cells["BN" + i].StringValue;
string BO = ws.Cells["BO" + i].StringValue;
string BP = ws.Cells["BP" + i].StringValue;
string BQ = ws.Cells["BQ" + i].StringValue;
string BR = ws.Cells["BR" + i].StringValue;
string BS = ws.Cells["BS" + i].StringValue;
string BT = ws.Cells["BT" + i].StringValue;
string BU = ws.Cells["BU" + i].StringValue;
string BV = ws.Cells["BV" + i].StringValue;
string BW = ws.Cells["BW" + i].StringValue;
string BX = ws.Cells["BX" + i].StringValue;
string BY = ws.Cells["BY" + i].StringValue;
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select [F1] from psa_bill where [BILL NUMBER]='{0}' and [BILL ITEM NUMBER]='{1}'", billNo, billItem);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
try
{
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
string A_t = SafeValue.SafeDate(A, new DateTime(1900, 1, 1)).ToString("yyyyMMdd");
if (A.Length > 10)
{
string[] ar_t = A.Split(' ');
if (ar_t.Length == 2)
{
DateTime dt_temp = SafeValue.SafeDate(ar_t[0], new DateTime(1900, 1, 1));
for (int A_i = 1; i < ar_t.Length; A_i++)
{
if (ar_t[A_i].Length > 0)
{
A_t = dt_temp.ToString("yyyyMMdd") + " " + ar_t[A_i];
break;
}
}
}
}
//ConnectSql_mb.cmdParameters cpar = new ConnectSql_mb.cmdParameters("@A", SafeValue.SafeDate(A, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
//ConnectSql_mb.cmdParameters cpar = new ConnectSql_mb.cmdParameters("@A", A_t, SqlDbType.NVarChar, 100);
ConnectSql_mb.cmdParameters cpar = new ConnectSql_mb.cmdParameters("@A", reGetDateFormat(A, file), SqlDbType.NVarChar, 100);
list.Add(cpar);
#region list add
cpar = new ConnectSql_mb.cmdParameters("@B", B, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeDecimal(E, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeDecimal(F, 0), SqlDbType.Float);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDate(G, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
cpar = new ConnectSql_mb.cmdParameters("@G", reGetDateFormat(G, file), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@H", H, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@I", I, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@J", SafeValue.SafeDecimal(J, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@K", K, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@L", SafeValue.SafeDecimal(L, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@M", M, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@N", SafeValue.SafeDecimal(N, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@O", SafeValue.SafeDecimal(O, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@P", P, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Q", Q, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@R", R, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@S", S, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@T", T, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@U", U, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@V", V, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@W", W, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@X", SafeValue.SafeDecimal(X, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Y", Y, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Z", Z, SqlDbType.NVarChar, 100);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@AA", SafeValue.SafeDate(AA, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
string _AA = reGetDateFormat(AA, file);
cpar = new ConnectSql_mb.cmdParameters("@AA", _AA, SqlDbType.NVarChar, 100);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@AB", SafeValue.SafeDate(AB, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
string _AB = reGetDateFormat(AB, file);
DateTime _ETA = new DateTime(S.Int(BP.Substring(10, 4)), S.Int(BP.Substring(7, 2)).S.Int(BP.Substring(4, 2)));
string ETA1 = _ETA.AddDays(-3).ToString("yyyy-MM-dd");
string ETA2 = _ETA.AddDays(3).ToString("yyyy-MM-dd");
string JOBTYPE = "IMP";
if (AT == "O")
{
JOBTYPE = "EXP";
}
string JOB = D.Text("select top 1 c.jobno from ctm_job j, ctm_jobdet1 c where j.jobno=c.jobno and j.etadate>'" + ETA1 + "' and j.etadate<'" + ETA2 + "' and c.containerno='" + I + "' AND (j.JobType = '" + JOBTYPE + "') ");
cpar = new ConnectSql_mb.cmdParameters("@AB", reGetDateFormat(_AB, file), SqlDbType.NVarChar, 100);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@AC", SafeValue.SafeDate(AC, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
cpar = new ConnectSql_mb.cmdParameters("@AC", reGetDateFormat(AC, file), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AD", AD, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AE", AE, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AF", AF, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AG", AG, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AH", AH, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AI", AI, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AJ", AJ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AK", AK, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AL", AL, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AM", AM, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AN", reGetDateFormat(AN, file), SqlDbType.DateTime);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AO", reGetDateFormat(AO, file), SqlDbType.DateTime);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AP", AP, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AQ", AQ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AR", AR, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AS", AS, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AT", AT, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AU", AU, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AV", AV, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AW", AW, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AX", SafeValue.SafeDecimal(AX, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AY", AY, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AZ", AZ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BA", BA, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BB", BB, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BC", BC, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BD", BD, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BE", BE, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BF", BF, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BG", SafeValue.SafeDecimal(BG, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BH", BH, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BI", SafeValue.SafeDecimal(BI, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BJ", BJ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BK", BK, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BL", BL, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BM", BM, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BN", BN, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BO", BO, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BP", BP, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BQ", BQ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BR", BR, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BS", SafeValue.SafeDecimal(BS, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BT", BT, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BU", BU, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BV", BV, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BW", BW, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BX", BX, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BY", BY, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JOB", JOB, SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into psa_bill (JOB_NO,F1,[BILL TYPE],[BILLING COMPANY],[BILL NUMBER],[BILL ITEM NUMBER],[ACCOUNT NUMBER],[BILL DATE],[REF NUMBER],[CONTAINER NUMBER],
[TARIFF CODE],[TARIFF DESCRIPTION],RATE,[UNIT DESCRIPTION],[BILLABLE UNIT],AMOUNT,[FULL VESSEL NAME],[FULL OUT VOY NUMBER],[FULL IN VOY NUMBER],
[ABBR VESSEL NAME],[ABBR OUT VOY NUMBER],[ABBR IN VOY NUMBER],[LINE CODE],[GROSS TONNAGE],LOA,[SERVICE ROUTE],[IN SERVICE ROUTE],[LAST BTR DATE],
[ATB DATE],[ATU DATE],[FIRST ACTIVITY DATE],[LAST ACTIVITY DATE],[CONNECTING FULL VSL NAME],[CONNECTING FULL OUT VOY NUMBER],[CONNECTING ABBR VSL NAME],
[CONNECTING ABBR VOY NUMBER],[CONNECTING SERVICE ROUTE],[CONNECTING IN SERVICE ROUTE],[CONNECTING VESSEL COD DATE],[CONNECTING VESSEL ATB DATE],
[SERVICE START DATE],[SERVICE END DATE],[LOCATION FROM],[LOCATION TO],[BERTH NUMBER],[SLOT OPERATOR],[LOAD/DISC INDICATOR],[FROM],[TO],[CNTR TYPE],[CNTR SIZE],
[ISO SIZE TYPE],[DG IMO CLASS],[TRANSHIP INDICATOR],[DEPOT INDICATOR],[REASON CODE],[LADEN STATUS],[CNTR OPERATOR],[GST INDICATOR],[GST PERCENTAGE],
[CURRENCY CODE],[EXCHANGE RATE],[ORG CODE],[CHARGE CATEGORY],[CHARGE TYPE],[CHARGE CLASSIFICATION 1],[CHARGE CLASSIFICATION 2],[CHARGE DESCRIPTION],
[DESCRIPTION LINE 1],[DESCRIPTION LINE 2],[DISCOUNT TARIFF CODE],[DISCOUNT PERCENT],[CUSTOMER REF 1],[CUSTOMER REF 2],[CUSTOMER REF 3],
[CUSTOMER REF 4],[CUSTOMER REF 5],[CUSTOMER REF 6]) values (@JOB,@A,@B,@C,@D,@E,@F,@G,@H,@I,@J,@K,@L,@M,@N,@O,@P,@Q,@R,@S,@T,@U,@V,@W,@X,@Y,@Z,
@AA,@AB,@AC,@AD,@AE,@AF,@AG,@AH,@AI,@AJ,@AK,@AL,@AM,@AN,@AO,@AP,@AQ,@AR,@AS,@AT,@AU,@AV,@AW,@AX,@AY,@AZ,
@BA,@BB,@BC,@BD,@BE,@BF,@BG,@BH,@BI,@BJ,@BK,@BL,@BM,@BN,@BO,@BP,@BQ,@BR,@BS,@BT,@BU,@BV,@BW,@BX,@BY)");
//ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["local"].ConnectionString);
SqlCommand cmd = new SqlCommand(sql, sqlConn);
sqlConn.Open();
foreach (cmdParameters par in list)
{
if (par.size == 0)
{
com.Parameters.Add(par.name, par.type);
}
else
{
com.Parameters.Add(par.name, par.type, par.size);
}
com.Parameters[par.name].Value = par.value;
}
object o = cmd.ExecuteNonQuery();
sqlConn.Close();
//if (re.status)
//{
successJob++;
//}
//else
//{
// errorDo++;
//throw new Exception(re.context);
//}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
if (B.Trim().ToUpper().Equals("BILL TYPE"))
{
beginImport = true;
}
}
}
re_text = string.Format(@"uploaded {0} jobs", successJob);
re_text += successItem > 0 ? string.Format(@" {0} items.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} existed", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} error", errorDo) : "";
error_text = re_text;
string user = HttpContext.Current.User.Identity.Name;
string sql1 = string.Format(@"insert into CTM_JobEventLog (CreateDatetime,Controller,JobType,Remark) values (getdate(),@Controller,@JobType,@Remark)");
List <ConnectSql_mb.cmdParameters> list1 = new List <ConnectSql_mb.cmdParameters>();
list1.Add(new ConnectSql_mb.cmdParameters("@Controller", user, SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@JobType", "PSA", SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@Remark", re_text, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult re1 = ConnectSql_mb.ExecuteNonQuery(sql1, list1);
}
protected void grid_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string pars = e.Parameters;
string[] ar = pars.Split('_');
if (ar.Length >= 2)
{
if (ar[0].Equals("Uploadline"))
{
#region
ASPxGridView grid = sender as ASPxGridView;
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxTextBox txt_Id = grid.FindRowCellTemplateControl(rowIndex, null, "txt_Id") as ASPxTextBox;
ASPxTextBox txt_ContNo = grid.FindRowCellTemplateControl(rowIndex, null, "txt_ContNo") as ASPxTextBox;
e.Result = txt_Id.Text + "_" + txt_ContNo.Text;
#endregion
}
if (ar[0].Equals("UploadCargoline"))
{
#region
ASPxGridView grid = sender as ASPxGridView;
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxTextBox txt_Id = grid.FindRowCellTemplateControl(rowIndex, null, "txt_Id") as ASPxTextBox;
ASPxTextBox txt_ContNo = grid.FindRowCellTemplateControl(rowIndex, null, "txt_ContNo") as ASPxTextBox;
e.Result = txt_Id.Text;
#endregion
}
if (ar[0].Equals("CargoListline"))
{
#region
ASPxGridView grid = sender as ASPxGridView;
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxTextBox txt_Id = grid.FindRowCellTemplateControl(rowIndex, null, "txt_Id") as ASPxTextBox;
e.Result = txt_Id.Text;
#endregion
}
if (ar[0].Equals("Confirmedline"))
{
#region
ASPxGridView grid = sender as ASPxGridView;
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxLabel lbl_JobId = grid.FindRowCellTemplateControl(rowIndex, null, "lbl_JobId") as ASPxLabel;
if (lbl_JobId != null)
{
string sql = string.Format(@"update ctm_job set JobStatus='{0}' where Id={1}", "Confirmed", SafeValue.SafeInt(lbl_JobId.Text, 0));
int n = ConnectSql_mb.ExecuteNonQuery(sql);
if (n > 0)
{
e.Result = "Action Success!";
}
}
#endregion
}
}
if (pars.Length >= 2 && (pars.Substring(0, 2) == "BR"))
{
#region Receive
string docnos = pars.Substring(2);
string[] docs = docnos.Split(new char[] { ',' });
int done = 0;
int all = 0;
string result = "";
if (docs.Length > 1)
{
for (int i = 0; i < docs.Length; i++)
{
string id = docs[i].Trim();
if (id != "")
{
all++;
string sql = string.Format(@"update job_house set CargoStatus='C',QtyOrig=Qty,WeightOrig=Weight,VolumeOrig=Volume where Id=@Id");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Id", id, SqlDbType.Int));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (res.status)
{
done++;
}
}
}
result = string.Format("Total received : {0} / {1}", done, all);
}
else
{
result = string.Format("Action Error ! Pls select at least one");
}
e.Result = result;
#endregion
}
}
public void ImportJob(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else if (file.ToLower().EndsWith(".xls"))
{
wb.Open(file, FileFormatType.Default);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
for (int i = 1; true; i++)
{
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string A = ws.Cells["A" + i].StringValue;
if (A.Trim().ToUpper().Equals("COMPANY"))
{
beginImport = true;
empty_i++;
continue;
}
try
{
A = ws.Cells["A" + i].StringValue;
}
catch
{
empty_i++;
continue;
}
if (A.Length <= 0)
{
empty_i++;
continue;
}
empty_i = 0;
if (beginImport)
{
string[] cellsList = { "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF",
"AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU" };
for (int j = 0; j < cellsList.Length; j++)
{
string code = "";
string value = ws.Cells[cellsList[j] + i].StringValue;
string header = ws.Cells[cellsList[j] + 1].StringValue;
if (value.Length > 0)
{
try
{
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
//ConnectSql_mb.cmdParameters cpar = new ConnectSql_mb.cmdParameters("@A", SafeValue.SafeDate(A, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
ConnectSql_mb.cmdParameters cpar = null;
#region list add
cpar = new ConnectSql_mb.cmdParameters("@Customer", A, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (header.Length < 4)
{
code = (header.Trim().Replace("'", ""));
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
}
else
{
code = (header.Substring(0, 4).Trim().Replace("'", ""));
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
}
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Description", header, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Price", value, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@LoadedTime", DateTime.Today.ToString("yyyy-MM-dd"), SqlDbType.DateTime);
list.Add(cpar);
#endregion
string sql_check = string.Format(@"select CustomerName from [dbo].[MastertRate] where [CustomerName]='{0}' and [Code]='{1}'", A, code);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
else
{
string sql = string.Format(@"insert into [dbo].[MastertRate] ([CustomerName],[Code],[Description],[Price],LoadedTime)
values (@Customer,@Code,@Description,@Price,@LoadedTime)");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
successJob++;
}
else
{
errorDo++;
//throw new Exception(re.context);
}
}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
}
//=====================检测是否存在Bill Number
}
else
{
}
}
re_text = string.Format(@"uploaded {0} rate", successJob);
re_text += successItem > 0 ? string.Format(@" {0} items.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} existed", existDo) : "";
//re_text += errorDo > 0 ? string.Format(@" {0} error", errorDo) : "";
error_text = re_text;
string user = HttpContext.Current.User.Identity.Name;
//string sql1 = string.Format(@"insert into CTM_JobEventLog (CreateDatetime,Controller,JobType,Remark) values (getdate(),@Controller,@JobType,@Remark)");
//List<ConnectSql_mb.cmdParameters> list1 = new List<ConnectSql_mb.cmdParameters>();
//list1.Add(new ConnectSql_mb.cmdParameters("@Controller", user, SqlDbType.NVarChar, 100));
//list1.Add(new ConnectSql_mb.cmdParameters("@JobType", "PSA", SqlDbType.NVarChar, 100));
//list1.Add(new ConnectSql_mb.cmdParameters("@Remark", re_text, SqlDbType.NVarChar, 300));
//ConnectSql_mb.sqlResult re1 = ConnectSql_mb.ExecuteNonQuery(sql1, list1);
}
private void Job_New_Save(DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
DateTime date = DateTime.Now;
string time4 = date.ToString("HHmm");
string jobType = SafeValue.SafeString(cbb_new_jobtype.Value, "IMP");
string jobStatus = SafeValue.SafeString(cbb_new_jobstatus.Text, "Quoted");
//string jobType1 = "IMP";
//if (jobType.IndexOf("EXP") > -1)
//{
// jobType1 = "EXP";
//}
string jobType1 = jobType;
string isTrucking = SafeValue.SafeString(cmb_IsTrucking.Value);
string isWarehouse = SafeValue.SafeString(cmb_IsWarehouse.Value);
string subContract = SafeValue.SafeString(cbb_Contractor.Value);
subContract = (subContract == "YES" ? "YES" : "NO");
string warehouseCode = SafeValue.SafeString(txt_new_WareHouseId.Text);
string jobno = "";
string user = HttpContext.Current.User.Identity.Name;
string quoteNo = "";
string quoteStatus = "Pending";
if (jobStatus == "Quoted")
{
quoteNo = C2Setup.GetNextNo("", "CTM_Job_" + jobStatus, date);
}
else
{
quoteStatus = "None";
jobno = C2Setup.GetNextNo("", "CTM_Job_" + jobType1, date);
quoteNo = jobno;
}
DateTime now = DateTime.Now;
string sql = string.Format(@"insert into CTM_Job (JobNo,JobDate,EtaDate,EtdDate,CodDate,StatusCode,CreateBy,CreateDatetime,UpdateBy,UpdateDatetime,EtaTime,EtdTime,JobType,ClientId,YardRef,PickupFrom,DeliveryTo,Remark,WarehouseAddress,JobStatus,QuoteNo,QuoteStatus,QuoteDate,WareHouseCode,IsTrucking,IsWarehouse,Contractor,DepotCode) values (@JobNo,@JobDate,@EtaDate,@EtdDate,@CodDate,@StatusCode,@CreateBy,@CreateDatetime,@UpdateBy,@UpdateDatetime,@EtaTime,@EtdTime,@JobType,@ClientId,@YardRef,@PickupFrom,@DeliveryTo,@Remark,@WarehouseAddress,@JobStatus,@QuoteNo,@QuoteStatus,@QuoteDate,@WareHouseCode,@IsTrucking,@IsWarehouse,@Contractor,@DepotCode) select @@identity");
if (jobType1 == "WGR" || jobType1 == "WDO" || jobType1 == "TPT")
{
sql = string.Format(@"insert into CTM_Job (JobNo,JobDate,EtaDate,EtdDate,CodDate,StatusCode,CreateBy,CreateDatetime,UpdateBy,UpdateDatetime,EtaTime,EtdTime,JobType,ClientId,YardRef,PickupFrom,DeliveryTo,SpecialInstruction,WarehouseAddress,JobStatus,QuoteNo,QuoteStatus,QuoteDate,WareHouseCode,IsTrucking,IsWarehouse,Contractor,DepotCode) values (@JobNo,@JobDate,@EtaDate,@EtdDate,@CodDate,@StatusCode,@CreateBy,@CreateDatetime,@UpdateBy,@UpdateDatetime,@EtaTime,@EtdTime,@JobType,@ClientId,@YardRef,@PickupFrom,@DeliveryTo,@SpecialInstruction,@WarehouseAddress,@JobStatus,@QuoteNo,@QuoteStatus,@QuoteDate,@WareHouseCode,@IsTrucking,@IsWarehouse,@Contractor,@DepotCode) select @@identity");
}
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", jobno, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@JobDate", txt_new_JobDate.Date, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@EtaDate", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@EtdDate", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@CodDate", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@StatusCode", "USE", SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@CreateBy", user, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@CreateDatetime", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@UpdateBy", user, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@UpdateDatetime", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@EtaTime", time4, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@EtdTime", time4, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", cbb_new_jobtype.Value, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@ClientId", btn_new_ClientId.Text, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@YardRef", txt_DepotAddress.Text, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@PickupFrom", txt_FromAddress.Text, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@DeliveryTo", txt_ToAddress.Text, SqlDbType.NVarChar));
if (jobType1 == "WGR" || jobType1 == "WDO" || jobType1 == "TPT")
{
list.Add(new ConnectSql_mb.cmdParameters("@SpecialInstruction", txt_new_remark.Text, SqlDbType.NVarChar));
}
else
{
list.Add(new ConnectSql_mb.cmdParameters("@Remark", txt_new_remark.Text, SqlDbType.NVarChar));
}
list.Add(new ConnectSql_mb.cmdParameters("@WarehouseAddress", txt_WarehouseAddress.Text, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@JobStatus", jobStatus, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@QuoteNo", quoteNo, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@QuoteStatus", quoteStatus, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@QuoteDate", now, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@WareHouseCode", warehouseCode, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@IsTrucking", isTrucking, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@IsWarehouse", isWarehouse, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@Contractor", cbb_Contractor.Value, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@DepotCode", btn_DepotCode.Text, SqlDbType.NVarChar));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteScalar(sql, list);
if (res.status)
{
int jobId = SafeValue.SafeInt(res.context, 0);
string userId = HttpContext.Current.User.Identity.Name;
C2.CtmJobEventLog elog = new C2.CtmJobEventLog();
elog.Platform_isWeb();
elog.Controller = userId;
if (jobStatus == "Quoted")
{
elog.setActionLevel(jobId, CtmJobEventLogRemark.Level.Quotation, 1);
elog.ActionLevel_isQuoted(jobId);
C2Setup.SetNextNo("", "CTM_Job_" + jobStatus, quoteNo, date);
auto_add_trailer_trip(quoteNo, jobType1);
//GetJobRate(quoteNo, btn_new_ClientId.Text, SafeValue.SafeString(cbb_new_jobtype.Value));
e.Result = quoteNo;
}
else
{
elog.setActionLevel(jobId, CtmJobEventLogRemark.Level.Job, 1);
elog.ActionLevel_isJOB(jobId);
C2Setup.SetNextNo("", "CTM_Job_" + jobType1, jobno, date);
auto_add_trailer_trip(jobno, jobType1);
e.Result = jobno;
}
elog.log();
}
}
private void Job_New_Save(DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
DateTime date = DateTime.Now;
string time4 = date.ToString("HHmm");
string jobType = SafeValue.SafeString(cbb_new_jobtype.Value, "IMP");
string jobStatus = SafeValue.SafeString(cbb_new_jobstatus.Text, "Quoted");
//string jobType1 = "IMP";
//if (jobType.IndexOf("EXP") > -1)
//{
// jobType1 = "EXP";
//}
string jobType1 = jobType;
string isTrucking = "No";
if (cb_Trucking.Checked)
{
isTrucking = "Yes";
}
string isWarehouse = "No";
if (cb_Warehouse.Checked)
{
isWarehouse = "Yes";
}
string isTransport = "No";
if (cb_Transport.Checked)
{
isTransport = "Yes";
}
string isCrane = "No";
if (cb_Crane.Checked)
{
isCrane = "Yes";
}
if (jobType == "IMP" || jobType == "EXP" || jobType == "LOC")
{
isTrucking = "Yes";
}
else if (jobType == "WGR" || jobType == "WDO" || jobType == "TPT")
{
isTransport = "Yes";
}
else if (jobType == "CRA")
{
isCrane = "Yes";
}
string warehouseCode = SafeValue.SafeString(txt_new_WareHouseId.Text);
string jobno = "";
string user = HttpContext.Current.User.Identity.Name;
string quoteNo = "";
string quoteStatus = "Pending";
if (jobStatus == "Quoted")
{
quoteNo = C2Setup.GetNextNo("", "CTM_Job_" + jobStatus, txt_new_JobDate.Date);
}
else
{
quoteStatus = "None";
jobno = C2Setup.GetNextNo("", "CTM_Job_" + jobType1, txt_new_JobDate.Date);
quoteNo = jobno;
}
DateTime now = DateTime.Now;
string sql = string.Format(@"insert into CTM_Job (JobNo,JobDate,EtaDate,CodDate,StatusCode,CreateBy,CreateDatetime,UpdateBy,UpdateDatetime,EtaTime,EtdTime,JobType,JobStatus,QuoteNo,QuoteStatus,QuoteDate,WareHouseCode,IsTrucking,IsWarehouse,IsLocal,IsAdhoc)
values (@JobNo,@JobDate,@EtaDate,@CodDate,@StatusCode,@CreateBy,@CreateDatetime,@UpdateBy,@UpdateDatetime,@EtaTime,@EtdTime,@JobType,@JobStatus,@QuoteNo,@QuoteStatus,@QuoteDate,@WareHouseCode,@IsTrucking,@IsWarehouse,@IsLocal,@IsAdhoc) select @@identity");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", jobno, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@JobDate", txt_new_JobDate.Date, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@EtaDate", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@CodDate", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@StatusCode", "USE", SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@CreateBy", user, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@CreateDatetime", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@UpdateBy", user, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@UpdateDatetime", now, SqlDbType.DateTime));
list.Add(new ConnectSql_mb.cmdParameters("@EtaTime", time4, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@EtdTime", time4, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", cbb_new_jobtype.Value, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@JobStatus", jobStatus, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@QuoteNo", quoteNo, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@QuoteStatus", quoteStatus, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@QuoteDate", now, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@WareHouseCode", warehouseCode, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@IsTrucking", isTrucking, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@IsWarehouse", isWarehouse, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@IsLocal", isTransport, SqlDbType.NVarChar));
list.Add(new ConnectSql_mb.cmdParameters("@IsAdhoc", isCrane, SqlDbType.NVarChar));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteScalar(sql, list);
if (res.status)
{
int jobId = SafeValue.SafeInt(res.context, 0);
string userId = HttpContext.Current.User.Identity.Name;
C2.CtmJobEventLog elog = new C2.CtmJobEventLog();
elog.Platform_isWeb();
elog.Controller = userId;
if (jobStatus == "Quoted")
{
elog.setActionLevel(jobId, CtmJobEventLogRemark.Level.Quotation, 1);
elog.ActionLevel_isQuoted(jobId);
C2Setup.SetNextNo("", "CTM_Job_" + jobStatus, quoteNo, date);
//GetJobRate(quoteNo, btn_new_ClientId.Text, SafeValue.SafeString(cbb_new_jobtype.Value));
e.Result = quoteNo;
}
else
{
elog.setActionLevel(jobId, CtmJobEventLogRemark.Level.Job, 1);
elog.ActionLevel_isJOB(jobId);
C2Setup.SetNextNo("", "CTM_Job_" + jobType1, jobno, date);
e.Result = jobno;
}
elog.log();
sql = string.Format(@"update CTM_Job set JobNo={0} where Id={0}", jobId);
ConnectSql.ExecuteSql(sql);
}
}